Cannot open user default database. Login failed. SQL 2005

Question

I have taken a copy of a database home with me so I can do some testing. However when I try to run a stored procedure I get Cannot open user default database. Login failed..

I have checked and checked and checked I can open tables in the databases login to sql management studio and access the default as well as other databases any ideas?

Possibly a corrupt user it was from sql 2000 at work to 2005 at home

Answer 1

EDIT: Mine was from 2005 to 2005. Not sure if this will work for your case...

I had a similar problem. For me, when I detach or create a back up and then re-create the database, it will loose connection to users. User I've been using is still there under Login but it would fail to log in.

In my case, I was able to log in by deleting the User under the database -> security -> users, not the user that's in the root sql server users list.

Then go to root users list and reassign database mapping or create user if not exists.

Hope this helps.

Answer 2

My understanding is that Logins are stored in the server, whereas a User is an assignment of a login to a database (correct me if I'm wrong).

Therefore, you cannot move Logins by detaching/attaching databases, and the solution would be to create a database User connecting a (valid) login to the copied database.

Answer 3

This is a shot in the dark, so forgive me if it just wastes your time.

Another poster mentioned that a given user has an id for the system and an id for any given database. This can be proven out by comparing sid's between the master.sys.syslogins and dbname.sys.users for the same login / user name. If you restore a backup from another sql server that has it's own copy of the master databases, the sids won't match.

Sql Server 2005 doesn't allow direct editing of system tables with out a lot of pain. To help out with these mis matches, they added a stored procedure to help you fix them:

USE dbName GO

sp_change_users_login @Action='Report'

That will show you what users have a dbName.sys.users entry, but no master.sys.syslogins one - or where the name exists in both, but differ by sids.

If it shows that your user is out of synch, the procedure also has a mode to change the linking:

USE dbName GO sp_change_users_login 'Update_One', 'userNameInDbUsers', 'UserNameInLogins'

If the sid mis-match isn't your problem, I've also seen really screwy stuff with Sql Server 2005. The gui is especially buggy. To fix a problem like this, I had to actually drop the syslogins entry (via the gui or DROP LOGIN command )

sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx

Drop Login syntax: http://msdn.microsoft.com/en-us/library/ms188012(SQL.90).aspx

Answer 4

As was mentioned before, the login mapping to that user account probably became disassociated during the move. Or, you moved it without creating the credentials it was expecting, in which case, you'd need to create the login first...

If it was a backup set and you are restoring it, however, there is no way (that I know of) to reassociate the login to the user via the management UI. Instead, you have to use:

exec sp_change_users_login update_one, 'user', 'login'

to get it to restore the link.

Answer 5

I moved 8 databases from SQL Server 2000 to SQL Server 2005 and onto a whole different computer. I normally like to know what stored procs are doing so I dug a little bit and found that the actual command is ALTER USER.

It's what everybody else has been saying. The users get disassociated when you detach and reattach databases in SQL Server 2005. I find this behavior most annoying, as I didn't see that behavior in SQL Server 2000.

The T-SQL to fix this issue looks like this:

USE AdventureWorks;
ALTER USER Mary5 WITH NAME = Mary51;
GO

This MSDN article talks a bit more about this:

http://msdn.microsoft.com/en-us/library/ms176060.aspx

Answer 6

ALTER LOGIN works only in SQL 2005 and up.

To change the default database for a user in 2000 use

EXEC master.dbo.sp_defaultdb @loginname = N'BuiltIn\Administrators', @defdb = N'master'

I found this out the hard way when I set the builtin\administrators account to default to the application db and it went Offline somehow and I could no longer login. Using Management Studio, you can set the option to login to master but you must run the above command before any other operation will work, less you get the default database is unavailable error.

Answer 7

http://benharrell.wordpress.com/2007/01/15/cannot-open-user-default-database-login-failed-login-failed-for-user-username-microsoft-sql-server-error-4064/