My general recommendation for SQL Server service accounts and accounts that have sysadmin fixed server role membership is to default those logins to the master database. After all, master will always be present. If master isn't present, you don't have a working SQL Server. But every once in a while you get into the situation where a privileged account is defaulted to a user database which no longer exists. This is a situation we encountered over the weekend. With SQL Server 2000, if you try to login with that account using Query Analyzer, the following error is the error is thrown:



And if you try to connect using Enterprise Manager (again, SQL Server 2000), you'll get this:



If you don't have another account to connect with, you may think you're stuck, but you're not. Thankfully, isql (not isqlW) will see the error and force the connection to the master database instead. For instance, a connection using isql where the default database no longer exists will result in the following:



In SQL Server 2005, if the login doesn't have permission to access the database, you'll see this error message:



On the other hand, if the database doesn't exist (as was the case with the SQL Server 2000 example), you'll see an error similar to the following.



In SQL Server 2005's SQL Server Management Studio, however, there is an option to choose what database to connect to. If you click on the Options >> button, a second tab appears. There, enter master for the database to connect to (highlighted below).



Once you've established a connection to the SQL Server where you can execute a query, here is where the system stored procedure sp_defaultdb is your friend. Reset the login's default database like so:

EXEC sp_defaultdb 'MyLogin', 'master'
GO

If you want to go with the new SQL Server 2005 syntax:

ALTER LOGIN MyLogin
WITH DEFAULT_DATABASE = master
GO

Technorati Tags: T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2000 | SQL Server 2005