SQL Server Map Login to User

I often find myself restoring databases from backup so that I can move a database from one server to another. The problem is that the backup has users that have permissions associated with them, but those users don’t link to the same logins on the new machine. Here is the stored procedure that links them together.

   EXEC sp_change_users_login ‘Auto_Fix’, ‘[[db user name]]’;

There are other options to the sp_change_users_login, but this is the one that I usually need. This is because the login names on my server and the usernames on the database are the same. If those names do not match and you want to link them, you need to run this script.

 EXEC sp_change_users_login 'Update_One', '[[db user name]]', '[[server login name]]';

Hope this helps.



Categories: Database

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: