August 25, 2010 at 2:57 am
Where do you place your SQL users?
I've been recommended to place all users in the Master database instead of in each database.
Anyone have any recommendations?
August 25, 2010 at 3:46 pm
Users are logins mapped to the dbs.
Users have to be mapped to their respective dbs with minimum required privileges.
Logins should not be mapped to the Master db unless there is a strong reason to do so.
Thank You,
Best Regards,
SQLBuddy
August 26, 2010 at 1:20 pm
Are you talking about the default database defined for a login? If so, then you are going to get different answers.
Setting the default database to the user database will prevent the users from logging into the system if that database is renamed, dropped, offline, etc... If the users only require access to that specific database - then that shouldn't be a problem.
However, if users access multiple databases on the instance - and you set the default database to a user database, and that user database is not available the users cannot login and won't be able to work with the other databases.
So, the answer really is: It depends...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 26, 2010 at 5:41 pm
Users will generally be placed in the databases they would need access to. they will be mapped to Logins which reside in the master database. if the Login does not exist, the User existence will be immaterial.
However, if you are talking about the default database when creating a new login, it depends. if you want the user to access the database through the application only and not log on to the SQL server, you should map it to a database it does not have access to. If you dont mind them logging on to, then you can leave it as default Master database.
August 27, 2010 at 11:57 am
I personally try to avoid giving access to Master, including making that the default database. I have seen a few instances (just after I arrived here) where people would accidentally add objects to Master when they thought they were in a different database [... ah the problems with not doing a USE DatabaseName; GO; ]
I have a "admin" database on all SQL Servers (DBA_Admin) which I make as the default for anyone requiring access to multiple databases (mostly developers). However, I also have roles setup in the database(s) to provide permissions on objects, especially in Production environment(s). Now if someone creates objects without paying attention to where they are, it is not a big deal. Otherwise Users default to their own database. 😀
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply