Search This Blog

Tuesday, February 12, 2008

Cannot open user default database. Login failed.

Well , there can be many different scenarios but I am pretty sure those will be simple to drill down ...

In my case the user had a group and there were 6 users in that group
The user created a login for that group in SQL Server and whenever the users wer etrying to login through SSMS they were getting the error below :

Msg 4064, Level 11, State 1, Server ServerName, Line 1
Cannot open user default database. Login failed.

out of 6 users only 4 were having this problem .So we changed the default database for the login to master.It still gives the same error.Then we then created a new login for one of the users from that group seperately and its default database was set to master .we logged in using that login successfully .that ws strange.We then deleted that login and again tried to login again via SSMS using same login thta we deleted .Ideally it should have given us an error something like "login failed for the user ....18456" but It was still giving us the same error .
Cannot open user default database. Login failed.
Login failed for user 'XXXX\YYYY'. (Microsoft SQL Server, Error: 4064)

This made us feel that this user should also a part of other groups and those groups must be pointing to some defult database that does not exist or in a condition described in KB307864 (

We then ran this query in master
select name,dbname from syslogins where dbname not in (select dbname from sysdatabases)
And we won the battle :D ...It showed us 5 logins which have a default database that does not exist.So we concluded that all or less users in the current group also exist in all or some of the other groups where the default database does not exist .We then changed the default database for those 5 logins to master and the issue was resolved.


No comments: