SQL Server login – default database and failed logins

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.

In most cases leaving the default database as master will be the reliable choice as the master database in terms of user connectivity, the reason being that if SQL is up so is the master database unless there is some other issue going on! With that said 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!

3 thoughts on “SQL Server login – default database and failed logins

Add yours

  1. Good thoughts. I had never considered this topic this closely. Thanks.

    You may want to consider having someone proofread your posts. I found it difficult to follow because many of the sentences had commas instead of periods. Some apparent sentences therefore didn’t get capitalized. I know some people don’t think this matters, but it helps the readability of the writing. It’s kind of like good code comments – you might be able to understand the code without them but it’s much easier if good comments are there.

    Like

Leave a Reply to Larry Huisingh Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: