We are setting up local sql server logins on a server that has multiple databases and have the situation where a piece of software will need to connect to more than one database, so I'm looking for some advice on which approach is better:
Simplifying our dilema, let's assume we have 1 application, 1 server, 2 databases on it:
Create a single APP_RW login which can be mapped to 2 databases.
Create 2 logins APP_DB1_RW and APP_DB2_RW, each of which is mapped to 1 database.
SQL Server allows both methods and there are obvious pro's and con's...application login's are simpler by having 1 account, small possibility of updating the wrong database table if tables are named identically in both databases.
I just wondered what the general best practice is?
I don't know what would be considered "best practice". But we have a great lead engineer, and he insists on using #2 exclusively. I'm a DBA, but it doesn't really matter to me.
This is a common situation in my company and the way we handle it:
App1 manages (INSERT, UPDATE, DELETE, indexes, sp's , UDF's, ...) dB1 and only reads from dB 2 (SELECT).
App2 manages (INSERT, UPDATE, DELETE, indexes, sp's , UDF's, ...) dB2.
We give db_owner rights to app1 on dB1 and db_owner rights to app2 on dB2.
We give additional db_datareader rights to app1 on dB2.
Normally you don't want two codes to manage the same data container. A data container could be a table or a whole database (dB).
A solution is that both apps use the same class to manage (change) data in a table,
or you don't allow "any other app" to change anything in the dB they don't manage.
A good solution, is to create some views in dB2 for other apps. Those other apps get read-only rights on those views.
I don't know if this is similar to your situation.
I try to make logins map to applications. So if you have one application, it would get the permissions it needs to run and nothing else would use that login. A second application would get its own login and a different set of permissions.
That makes it easy to review what permissions applications need (for me and auditors). It makes it easy to reset passwords since they are used in hopefully very few places.
And I prefer domain accounts to local SQL Server logins but it sounds like that isn't an option.
I'm curious about your question about updating the wrong database. Is this a multi-tenant situation?
No, you should never give dbo rights to an app account/login. It never needs them (except perhaps during the installation.) Dbo could allow someone to drop the db or a add a rogue account.
We did have one situation where they (their deploy package) unintentionally dropped their own database. I'm not a DBA, just the guy that doesn't run away screaming when someone needs a backup or an account.
db_owner was a fast way for me to give them all the rights to create/drop/redesign tables, indexes, UDF's, SP's, INSERT/UPDATE/SELECT/DELETE...
What rights do you give the developers?
Do they deploy sql artifacts? Or you have other deployment mechanism?
They develop in Ruby on Rails. Changes to the database happen trough migrate scripts.
And who runs those scripts on the dev environment?
The programmes. They all use the same account (db_owner).
The DevOp runs those scripts on the ACC and PRD dB. Each dB (DEV, ACC, PRD) has its own account/password.
So devs know this user/password (sql or active directory?) log in as that user and deploy the scripts?
The developers know the account/pwd of the DEV database.It's a SQL Server login/user.
That's a different approach than what I am used to. All devs in dev environment have either permission to dev server or we use a Ci/cd deploy process once they submit their code to a source control such as gitlab.
But also we have some devs with sql server installed locally where they can test the deployment. Nothing is pushed to dev server that does not work on local sql server
Great, thanks for the replies.
Sounds like it should be individual User ID / Password for each database rather than having a single User ID which has access to both.
No dbo privs will be given to accounts the developers use apps they write...no need for that.
The example I was thinking of has DB1 with transactional tables in it and DB2 being a reporting database with data loaded from DB1 and transformed into dimension/fact tables via an overnight load process...occasionally tables are named the same in both databases so I was being cautious, maybe over cautious, but I’d rather we get the design right now than have to restore data in the future!
Even if you are going to have one user mapped to both the databases, you still have the option to grant permissions separately to individual databases.
So, security is still intact with one user for multiple databases.
In a data warehouse scenario table naming convention standard best practice is DimCompanies FactSales. Why do you have some table named the same in oltp and OLAP db?
True, and the dimxx,factxx tables are in place in our reporting database, however there are the odd few same named tables. I think the safest approach will be to have separate credentials for each database to rule out the possibility.
You can have an "employee", "sale", "shop", "client", ... table in every one of the100+ databases in your company without any problem. There is absolutely no need to give each of your tables a unique name across all your databases. A unique name within each database is all you need.
A data warehouse database will of course have its own naming conventions, that will most likely differ from the naming conventions of OLTP databases.