Re-associate Microsoft SQL Login with a Database User

I’ve had to search for this several times in the last few weeks, (ever since Mark Drew pulled me into the dark side that is local development.) I figured if I blog it, I’ll always have the answer at hand.

If you are restoring a Microsoft SQL database from a backup onto a new server, and you need to re-associate the server login name to the database username, you need to call the “sp_change_users_login” stored procedure. That will allow you to reconnect them.

Published by tpryan

2 thoughts on “Re-associate Microsoft SQL Login with a Database User”

  2. Another option i just read about was if you create a user with a known SID (i believe it’s an option of the CREATE LOGIN or sp_add_login procedures in SQL 2005 and SQL 2000 respectively).

    That way you can re-create the login on the new SQL Server with the right SID and SQL will automatically associate the Login with the DB user.

    Not as useful in a DR scenario; the sp_change_users_login procedure is still your best bet.

    Like

    Reply

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s