ALTER LOGIN fix for SQL Server logon problems

We’ve all done it. Changed our default database for our logon to a database in SQL Server other than master, then either detached or deleted the database some way down the line. Of course at this point your pretty much screwed as you can’t access much as your login no longer works.

To fix this when you open Management Studio press the Options button and change the Connect to Database option to master. Now, this doesn’t fix the problem, it merely allows you to then open up a New Query. In the query enter the following SQL, change the login name and execute it.

ALTER LOGIN TestLogin WITH DEFAULT_DATABASE = master

Change TestLogin for your own login, close Management Studio and re-open it, press the Options button and change the Connect to Database option back to <default>.

At this point you should be able to get back into SQL Server with the usual authority and you can now re-attach your database.

Tagged . Bookmark the permalink.

Leave a Reply