Eric Johnson : Security, Logins

When a user logs on to SQL Server, they receive the error “Cannot open user default database. Login failed.” What’s wrong?

ejohnson2010 — Tue, 28 Oct 2008 03:47:52 GMT

Good Question! This is one of those error messages that you will eventually encounter in SQL Server if you work with it enough. A login’s default database is a delicate thing that will prevent them from being able to logon if there is a problem. The default database defines which database the user will be automatically logged into if they connect and they don’t specify a database. Several things can cause the above error message. The most common is a login that does not have access to the database that is configured as its default. This is easily remedied; either set up a corresponding user for the login in their default database or change the default to a database to which the login has access. This error will also rear its ugly head if you drop a database that was configured as the user’s default database. There is no check when a database is dropped to make sure it won’t affect users, so dropping someone’s default database will leave them in the dark when it comes to logging in. As I said, this only happens if the user’s connection method does not specify a database. Many applications will provide the database name and will be immune to the issue. The fix is simple, make sure the logon has access to their default database and make sure the default database exists.

This problem can get a little worse if you break the default database of your system administrator accounts, such as your Windows account that has ‘sa’ level rights or the ‘sa’ login itself (and no other logins have the permissions to fix the problem). In this case, the fix is still simple, but you need to use an application that allows you to specify a database, such as SQLCMD. SQLCMD is a command-line tool that comes with SQL Server 2005 and 2008. When you login, you can specify a database to use that will override the default. So to fix your problem, login with SQLCMD as follows:

sqlcmd -S sql2008 –U sa -P password -d master

This will let you login and use the master database despite the incorrect default database. To fix your login simply run the following query from the SQLCMD command prompt (you can use any database that exists and to which the login has access, master is used as an example):

ALTER LOGIN sa WITH DEFAULT_DATABASE=master

That’s it, the next time you login your new default database of master, or whatever you specified, will be used.

Fixing Orphaned Users

ejohnson2010 — Fri, 17 Oct 2008 16:32:28 GMT

This is a problem that plagues DBAs everywhere. When you restore a database, you run the risk of orphaning the users in the database. All users are linked via a SID to a login and if you have SQL Server logins, who’s SIDs are managed by SQL Server, you are at risk. Typically a restore to the same server from which the backup was taken won’t cause an issue unless you dropped and recreated the login. Generally the problem rears its ugly head when you restore a backup to a server that was not the original location. You planned ahead and created the same logins on the new server as existed on the old server, so why do the users end up orphaned? As I mentioned earlier, SQL Server manages the SIDs for SQL Server logins so there is no guarantee that the new login has the same SID as the original login did. Then when you restore your database, the users in that database are expecting SIDs that are not there and the next thing you know you have orphaned users. Just a note, this does not occur with Windows Logins because the SID is controlled by Windows or Active Directory. Unless you drop and re-create the user in Windows, the SID of an Active Directory user will be the same on all SQL Servers and hence your user accounts see the SID they are looking for. So, the million dollar question is, how do you fix the problem without dropping and re-creating the user and messing up the permissions in the process? Microsoft provides us with a handy little stored procedure called sp_change_users_login that you can use to fix orphaned users. This procedure can do several things; it can tell you which users are orphaned, it lets you fix an orphaned user manually, and it can attempt to automatically fix your issues. So let’s look at an example. I have deliberately orphaned a user called Annie in the AdventureWorks2008 database. When I run sp_change_users_login with the REPORT option, I can see that I indeed have an orphaned user.

EXEC sp_change_users_login 'REPORT'
…
UserName UserSID
-------- -----------------------------------
Annie 0xA5B5548F3DC81D4693E769631629CE1D

To fix this orphaned user all I have to do is run sp_change_users_login with the UPDATE_ONE action and tell SQL Server the name of my orphaned user and the name of the appropriate login.

EXEC sp_change_users_login 'UPDATE_ONE','Annie','Annie'

There you have it, a simple quick fix to orphaned users that you can use next time you have an issue. I just want to add one more thing regarding the AUTO_FIX action of sp_change_users_login. If you use this option, the procedure tries to automatically fix your orphaned users by matching user name to login name. If no match is found, it will create the appropriate login for you. The only reason I don’t like it is that is has the potential to create logins you don’t want, especially if your login names happen to deliberately differ from your user names.

Scripting SQL Server Logins

ejohnson2010 — Tue, 30 Sep 2008 19:58:00 GMT

You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

Understanding Login Components

To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.

SP_HELP_REVLOGIN

SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.

As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:

sp_help_revlogin 'SQLScript'

RESULTS:

/* sp_help_revlogin script

** Generated Oct 30 2007 9:23AM on laptop1 */

-- Login: SQLScript

CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.