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 default database becomes unavailable.
There are going to be situations where setting a default database other than the master database is essential. There are going to be situations where leaving the default database as master suits best.
The decision you make 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. Upon initial investigation everything appeared fine, the user account was enabled the password hadn’t been changed, The database the user was 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, the problem is this was not the database the user was trying to connect to at the time despite it being set as the default database for the login.
The expected behavior for the login was to be able to access all of their databases on the server 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 when the login was initially created. When this login was created only one user database existed so the admin who configured the login didn’t think twice about setting their user database as the default database for the login, unfortunately this setting was forgotten as more databases were added to the instance and this is when the issue became apparent.
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
- possible even single user
The login/user will lose access to SQL server when they try and make a new connection
Thanks for reading!