Cannot open user default database? Login failed?

Assigning a default database to every server principal (login) is good practice, no doubt. Just think of the last time someone in your organization created a user object in master by mistake. Generally, the most appropriate database to set as the default for a user is the database they will most likely access when performing their work, and for most cases that would be a user database (rather than a system database).

The default database – set for each server principal – is the database used when a user connects to the SQL Server instance, unless another database is specified when the connection is initiated (e.g. through the application's connection string, in the Connect to... dialog in SQL Server Management Studio, etc.).

Databases are not forever

While system databases tend to be present at every instance, the same cannot be said for user databases. For whatever reason, sooner or later a database will be relocated to another server or abandoned altogether, in some cases even simply renamed.

For instance, let's imagine that we've created two databases: database D1 and database D2 on the same server instance, and we've made it a rule to assign D1 as the default database for every login we create. Later we could decide to move D1 to a new server. How would that affect the default database setting for the logins at the old server?

Dropping or detaching a database does not affect default database settings. In fact, users trying to connect to the old server might even be unpleasantly surprised by the following error message:

Cannot open user default database. Login failed.
Login failed for user '<login>'. (Microsoft SQL Server, Error: 4064)

The default database setting specifies which database the user will connect to by default. Since the user can (and should) always specify the database context when connecting, this default will only be used in the case when no database has been specified.

About SQL Server Error 4064 (DB_UFAIL_FATAL)

However, a non-existing database is not the only possible reason for this particular exception; there are more possibilities to consider:

• Does the database exist? We cover this later in this post;
• Is the database online? There are 6 states that a database can be in (more details in Books Online), so verify the state of the database:
  

  select sys.databases.name
   ,sys.databases.[state]
   ,sys.databases.state_desc
   from sys.databases

• Is access to the database restricted? There are 3 access modes that can be set for a database (more details in Books Online), so verify the access mode:
  

  select sys.databases.name
   ,sys.databases.user_access
   ,sys.databases.user_access_desc
   from sys.databases

• Does the user have the CONNECT permission on the database? By default every database principal, i.e. a user (created from a server principal, i.e. a login) is granted the CONNECT permission implicitly, but this permission can also be denied. I've discussed permissions and how to check them for a particular user in a previous post.

Exception 4064 is also documented in more detail at the Events and Errors Message Center, a service provided by Microsoft TechNet.

Let me in!

Once we've determined that the reason for the error is in fact in the default database settings referencing a missing database, we should change them appropriately for each individual login.

The next query returns a list of logins with a default database setting referencing a database that cannot be found on the current instance:

select sys.server_principals.name
 ,sys.server_principals.sid
 ,sys.server_principals.default_database_name
 from sys.server_principals
 where (sys.server_principals.[type] in ('S', 'U', 'G'))
  and (not exists (
   select *
    from sys.databases
    where (sys.databases.name = sys.server_principals.default_database_name)
   ))

The results of the query are restricted to only list server principals that are either a SQL login ("S"), a Windows login ("U"), or a Windows group ("G").

To allow the users to connect to the server without specifying a database (by relying on the default database setting) we need to assign an existing default database to each one of their logins – which is quite easy once we (a) know who they are, and (b) determine which database is the most appropriate to be used as default.

If you for some reason don't have time to think about which database should be the default for every login, or simply don't care which one it is (as long as it's not master, model or msdb), here's a little script that (using the query above) sets tempdb as the default database for each login that currently has their default database set incorrectly.

Why tempdb? It exists at every instance and is accessible to any login. Keep in mind though that this is a quick fix, not a best practice.

ML