SS Cannot open user default database

Sql Server 2005: Cannot open user default database: Login Failed

Last month I attended training for Microsoft SQL Server 2008 Administration. It was a nice experience and something new for me to learn. During the training I was thanking god that I took a right direction in taking oracle as my career path than microsoft technology. :-)

Immediately after joining back in office I was given a task to move the entire sql server database from c-drive to f-drive for a reporting/auditing application called SCOM. It was quite easy to DETACH and ATTACH the database from c-drive to f-drive. But I faced a problem when I detached a database that was a default database. It was throwing following error when I tried to start the management studio,

"Cannot open user default database, login fails"

After some links on the web, I could resolve the issue,

I used the sqlcmd utility to change the default database in SQL Server 2005 to master database.
To do this, I follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

• If the SQL Server login uses Microsoft Windows authentication to connect to the instance which my instance was using, i typed the following at the command prompt, and then pressed ENTER:

sqlcmd –E -d master
ALTER LOGIN [QIA\OpsMgrAdmin] WITH DEFAULT_DATABASE=master
GO

• If the SQL Server login used SQL Server authentication to connect to the instance, then I would have typed the following at the command prompt, and then pressed ENTER:

sqlcmd -S InstanceName -d master -U SQLLogin -P Password
ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
GO

sqlcmd -S InstanceName -d master -U sa -P Password
ALTER LOGIN sa WITH DEFAULT_DATABASE = master
GO

Anyway it was nice experience and the task was completed after following the above commands to troubleshoot the issue. Thanks to the training to understand Microsoft Sql Server 2008 Database architecture.