Ok, I was testing something on one of my servers and made the mistake of setting the default database for sa to a user database rather than master. I changed it back in SSMS under Security/Logins/sa, by choosing master from the default db pulldown.
I thought this fixed it, but apparently not.
A little bit later I delete the user database that I had previously set as the sa default. Now, everything I try to do I get "Cannot open user default database...login failed for user sa". If I shut down SSMS, I can only reconnect by choosing
"master" as the default db. But even once in, I can't perform any actions without getting the error msg above.
I tried running the following query to fix it:
ALTER LOGIN sa WITH DEFAULT_DATABASE = master
exec sp_defaultdb 'sa', 'master'
And even a:
EXEC sp_change_users_login @Action='Auto_Fix', @UserNamePattern='sa', @Password='fof!master0'
In case this was some user/login orphan issue. No go.
This is SQL 2005. I'm sure there is some easy fix, but its beyond me. Can anyone help?
View Complete Post