Here are a few handy SQL commands that you may find useful (for SQL Server 2000).
Have you ever needed to list all databases on the current instance of SQL Server? You've probably not but this code will do it all the same ;)
exec sp_MSforeachDB "sp_helpdb ?"
What about listing detached users? Detached users are common when doing incomplete restores where the master database is corrupt:
exec sp_change_users_login 'Report'
Fixing your detached users could not be simpler, you can attempt to automatically fix all users:
exec sp_change_users_login 'Auto_Fix'
This instructs SQL Server to make a best guess at which database users match which logins, I find this rarely works well and in some instances won't work at all, so a manual approach is usually better:
exec sp_change_users_login 'Update_One', 'DemoUser', 'DemoLogin'
Here we are telling SQL Server to pair the database user DemoUser with the system login DemoLogin.
I Hope this is of some use to somebody, I'll check back and update this page every now and again with more useful tips.
- Andy
AndrewGail
3 Comments
Did you know that posts like this go straight to your MyWoyano page? It's your space to highlight your unique skills and knowledge. Drive traffic to your own webpage from the Woyano audience. Have a look at Andy's page, www.woyano.com/AndrewGail
Hey you know AdGuy always gets the last word! ;)