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

What Permissions Does a Principal Have?

SQL Server Management Studio provides several ways of checking which permissions have been granted (or denied) to whom and on what objects. This is achievable on different levels within the securables hierarchy:

• Server-level permissions, per principal: on the Permissions page of the Server Properties window (accessible through the View menu when the instance node is selected in the Object Browser pane);
• Database-level permissions, per principal: on the Permissions page of the Database Properties window (accessible through the View menu when the database node is selected in the Object Browser pane);
• Object-level permissions, per principal: on the Permissions page of the Object Properties window (accessible through the View menu when an object node is selected in the Object Browser pane.

Alternatively, the Login Properties and Database User Properties windows (i.e. the Securables page) provide an overview of permissions that have been granted (or denied) to an individual Server or Database Principal, respectively. Note, that only explicit permissions are shown here, not including permissions granted/denied by default, permissions granted/denied implicitly (e.g. inferred higher in the securables hierarchy, or via the membership in built-in or user-defined server and/or database roles), etc.

In other words: viewing permissions one-object-at-a-time using SSMS *is* possible, but for a comprehensive overview of permissions granted or denied to a specific principal, T-SQL provides more powerful methods.

Beyond the GUI

SQL Server 2005 introduced an extremely useful (and long-sought after) system function that lists the permissions granted (or denied) to a given principal on a given securable. This has ever since made it quite easy for any principal to check their own permissions on individual server-level or database-level securables.

I am referring to the sys.fn_my_permissions system function.

Through impersonation (e.g. using the EXECUTE AS statement) a principal with sufficient privileges (e.g. a DBA) can check the permissions of other principals as well. In fact any principal with the IMPERSONATE permission on another principal can do so.

What? How? A permission on a principal? Whether an object is a principal or a securable, depends on the point of view, and while every principal is also a securable, not every securable is a principal. Now say that out loud seven times. You can read more about Principals, Securables, and Permissions in Books Online. The entire Securable/Permission hierarchy is also illustrated in BOL.

However, even through impersonation only a single securable at a time can be checked using sys.fn_my_permissions.

To view the permissions for several objects summarily, two additional requirements must be met:

• Be familiar with the CROSS APPLY operator; and
• Prepare a list of all relevant securables represented by their names and classes.

Constructing the list of server and database securables is not a big task, once we (a) identify which securables are relevant, (b) learn where to find information about them, and (c) determine how to classify them appropriately. As always, Books Online is your friend. And, sometimes, so am I.

I've created the query which you're very welcome to download, and I'll keep it updated as SQL Server evolves. Unless I don't, which could happen, but won't.

Securables? What Securables?

The query returns a list of all relevant securables by name and class, exactly as expected by sys.fn_my_permissions. If you disagree, please, tell me, and I'll do my best to make it more agreeable.

In order to actually see the permissions and their state we need to apply this function to the list of securables, and – ta-da! – execute it while impersonating the principal we're interested in. The permission to impersonate the target principal is assumed, of course.

You're also very welcome to download the complete script.

The Rocky Road

As has become the natural way of things on this planet lately – some limitations do apply:

• The EXECUTE AS statement expects as its sole argument a reference to a singleton principal (a login – based on a Windows user or a SQL Server Login – or a user), not a grouping principal (a role or a login based on a Windows group), so take this into account;
• This query relies on impersonation, which means that only a single principal at a time can be processed – a limitation that can be easily overcome by an appropriate client application invoking the query for each principal in question in a loop;
• The query is executed in the context of the current database which means that the information is available only for objects of the current database. This limitation can also be overcome by a client application looping through the databases one might be interested in, but bear in mind that the function cannot "see" beyond the current instance – permissions on linked servers cannot be checked;
• If the argument supplied to EXECUTE AS is a LOGIN, the query will list the permissions of the server principal; similarly if the argument is a USER, only the permissions of the database principal will be listed.

The Singleton Principal Principle

How do we check the permissions of a role? The only way I can think of is to create a singleton principal (a login and/or a user) on-the-fly, not give it any explicit permissions and make it a member of the role in question. Then execute the script in the context of this new singleton principal.

And Windows groups? Well, we can do pretty much the same, but with one additional step (create an underprivileged Windows account first – provided that the OS administrator agrees).

Again, if you have better ideas, know that to share is divine.

Default Permissions – An Ultra Quick Look

When a login (server principal) is first created (on SQL Server 2005 and later), some permissions are inferred by that very act:

1. On the server instance:
   • CONNECT SQL – what good is a login that cannot connect to the server?
   • VIEW ANY DATABASE – by default every new login can view all other databases on the server;
2. On four default endpoints¹:
   • CONNECT – obviously;
3. On logins:
   • CONTROL – by default every server principal has complete control over itself.

When a user (database principal) is created, some database-level permissions follow suit:

1. On the database:
   • CONNECT – the elementary privilege of any database principal;
2. On users:
   • CONTROL – by default every database principal has complete control over itself;
3. If diagramming has been enabled on the database, each database principal by default also receives EXECUTE permissions on modules used in creating and maintaining database diagrams (dbo.fn_diagramobjects, dbo.sp_alterdiagram, dbo.sp_creatediagram, dbo.sp_dropdiagram, dbo.sp_helpdiagramdefinition, dbo.sp_helpdiagrams and dbo.sp_renamediagram).

So, what permissions do you have? And what permissions have you given your users?

ML

¹ Four default Endpoints exist on every instance through which connections can be made to the server: TSQL Default TCP, TSQL Default VIA, TSQL Local Machine and TSQL Named Pipes. See Network Protocols and Network Libraries in Books Online for details.