Linking ALL Users to Login after restoring a SQL Server 2005 database

Question

(Note this question asks about linking ALL Users, unlike the possible duplicate that asks about linking a single user)

I wish to move a database between two servers, I have backed the database up from the first server and done a database restore on the 2nd server, so far so good.

However our application makes use of a lot of database users that are defined in the database. These have to be linked to logins that are defined in the master database. The server I have restored the database to has all the logins defined, however they have different sids.

I am not a T-SQL expert….

I think sp_change_users_login is part of the solution, but I can't find out how to get it to automatically link all users in the restored database to the login of the same name.

The database creation scripts we use for our application create the users and logins, however it does not specify the SID when creating the login, hence this problem. Now if I had a time machine...

(When I Google I get lots of hits, however they are mostly sites that won't let you see the answer without having to register on the site first.)

Answer 1

Yes, you can do that by executing:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName'; 

However if your question was can I fix all users automatically then this won't do that.