Quick, DBA with the sysadmin role in SQL Server: What’s your default database?
I’ll bet you think it’s the master database. But, as I learned this weekend, that’s not something you can take for granted — especially if you didn’t create the login. Our clients typically create logins for XTIVIA DBAs, and in the case at hand the XTIVIA login’s default db was set to a user database.
I logged into the server Saturday with instructions to set this user db to single_user mode, rebuild an index and then set it back to multi_user mode. Unfortunately, once the db was in single_user mode and the index script ran, I lost my connection to the database and couldn’t log back in to Management Studio (login failed with error 4064). So I couldn’t set the db back to multi_user. Not good.
To recover, I logged into the database using SQLCMD, specifying a connection to the master database. Once in, I changed my default db to master, and we were once again all smiles in DBville.
Here’s the command (for a named instance):
C:Userskmaher>sqlcmd -S [serverinstance] -d master
1> alter login [domainlogin] with default_database = master
2> go