Friday, May 4, 2007

Re-connect users to logins

The stored proc built into SQL 2000 and SQL 2005 allows you to re-connect logins to their database users. The database user and SQL Server logins get disconnected you restore a database from a backup. One symptom of this is you can see that the database has a user that you expect, and SQL Server has a login with that name that you expect, but you can't login. Note that you must be connected to the current database that the database user resides. The example below reconnect them. You will need to do this for each user that is not connected anymore. use MyRestoredDB go sp_change_users_login 'Update_One', 'MyUserName', 'MyLogin'

No comments: