August 11, 2006 at 9:01 am
I had an interesting discussion with another DBA about the "default database" setting for logins in SQL Server. This is one of those "opinion" areas, so I was just wondering what some of you think.
On a QA or development server, it is my opinion that wherever possible, the default database setting for an application service account should be set to "master". This has two benifits. 1. It makes sure the application developers are supplying a database name in the connect string and / or have proviced some facility within the application to make this configurable. 2. It ensures that the login will still work in a "fluid" development or QA environment where the database itself may be dropped or added at any time and avoids the "orphaned" login situation where the login has the default database set and the database is dropped or renamed.
Of course, this flies in the face of the SQL Server operations guide best practice of setting the default database to something other than "master".
What are your thoughts? Remember, I'm limiting the discussion to development or QA environments. Production environments may have a completely different approach.
August 11, 2006 at 12:45 pm
I just prefer using the primary database they will access myself. Especially since I have to script it and release from Dev to QA (Or UAT) thru the DBA and all of that has to be hand coded beforehand so I script to test the code multiple times and build with the default set.
August 11, 2006 at 1:54 pm
Here's another thought.
There's no way you can keep logins from accessing MASTER. If the logins are not SYSADMIN role, they won't be able to create any objects in MASTER. However, they can still select and execute master objects.
I dunno, but it seems like it would be less cumbersome to just leave the default database set to master for all logins. The official SQL Server 2000 Operations Guide from Microsoft doesn't reccomend doing it, however seeing that you really can't keep logins from accessing MASTER by default, it seems rather pointless.
Now I could see why you would set it if you had a business need to set it. However I'm approaching the argument from a technical angle. Personally, I really don't understand why Microsoft even requires a default database to be set for a particular login. Its nice to have the option to set it for logins / users that are too lazy or "newbie" to set the database login context (again, a business driver). However it seems to encourage some extremely foul developer behavior when it comes to coding and configuring connection strings within an application.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply