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.

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

  1. 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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s