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.

1 comment:

farriahgaglio said...

Harrah's Resort Southern California - MapyRO
Harrah's Resort Southern California 태백 출장샵 is a Casino 제주 출장마사지 in Southern California, near 안산 출장마사지 Funner, California. Mapyro 보령 출장마사지 provides complete driving directions and road conditions. 서울특별 출장안마