Tuesday, May 7, 2013

MS SQL - Cannot Connect to DataBase; Cannot open user database. Login Failed. Login failed for user 'userName'. (Microsoft SQL server, Error 4064)

Problem Description: After droping a database (DB) without checking if that DB was the default DB of the account used, once will find they cannot log back into the SQL Studio to connect to SQL server. Pop-up with information similar to the following will appear:

Cannot Connect to DataBase; Cannot open user database. Login Failed. Login failed for user 'userName'.  (Microsoft SQL server, Error 4064)

Cause: Accidently DB that was droped was used as default DB for the account used.

Solution: SQLCMD can be used to connect to master or tempDb and then ALTER Default_Login DB for the user. Following is an example:

C:\> SQLCMD -E -D master
1> alter login [Domain\ServiceAccount] with default_database=master
2> GO
3> exit

C:\>

This should allow the connection back to the SQL Server.

Note: Same action can me performed by SQL Studio configuration. I will update that later on with screenshots.

No comments: