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

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