This is one of them little options that I see which quite often gets little consideration or gets set to a user database without consideration of what the consequences may be if that DB becomes unavailable. There are going to be situations where setting a default other than master is essential and there are going to be situations where leaving as master suits best and this comes down to the individual requirements of each login, Recently I had to fix an issue with user connectivity for a single login, the user was getting failed connections when trying to connect to the SQL server when trying to access one of their legacy databases , everything appeared fine – User account was enabled the password hadn’t been changed and was therefore correct, the database they were trying to access was up and accessible but the SQL error log highlighted the real issue.
Login failed for user ‘MyLogin’. Reason: Failed to open the database ‘TheDefaultdatabase’
Ahhh makes sense now because at the time that database (the default database for the login) was in the middle of a restore as part of some planned work, problem is this was not the database the user was trying to connect to at the time, the expected behavior for the login was to be able to access all of their databases regardless of any of the other databases not being available. Easy fix for this situation was to set the default database to master but could have been avoided if set correctly in the beginning, however when this login was created only one user database existed so the admin who configured the login didn’t think twice about setting the login to have a default database of their single user database, unfortunately this setting was forgotten as more databases were added to the instance.
In most cases leaving it as master will be the reliable option as the master database in terms of user connectivity because if SQL is up so is the master database unless there is some other issue going on! however you may have valid reasons to want to assign a login a specific default database and that’s cool provided you consider what will happen to these logins when the database becomes unavailable.
I checked BOL , unfortunately this only provides the following:
DEFAULT_DATABASE =database Specifies the default database to be assigned to the login. If this option is not included, the default database is set to master
Unfortunately there is no real warning there to allow you to give this setting good consideration, but it is pretty important to ask yourself the following question when creating a new user login.
Does it matter if the user/login cannot access the SQL server if the default database is inaccessible when making new connections?
If the answer is no then you can set to whichever database makes the most sense or leave as the default.
If the answer is yes then you might want to consider master as the default database if the login is granted permission to more than one database on the instance because when the default database becomes inaccessible i.e
- Recovery pending
- suspect
- offline
- restoring
- possible even single user
The login/user will lose access to SQL server when they try and make a new connection
Thanks for reading!