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.
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
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.
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.