Tuesday, August 11, 2009

Re-link database user after restoring a MS SQL Server database

If you have a database (let’s say a production server call MyDB) that has SQL Server users that have logins into a database. For example let’s pretend the login name is called MyAppUser.

When I take a backup of MyDB and then restore in to another server (let’s say my development database server) the user MyAppUser will not be able to login to my server. The reason is that the SQL Server login and the database User records have become disconnected.

To reconnect them (assuming the name is the same in both systems), you can use the following T-SQL to link them again.

use MyDB
sp_change_users_login 'Update_One', 'MyAppUser', 'MyAppUser'

1 comment:

gibsonherry said...

Graceful written content on this blog is really useful for everyone same as I got to know. Difficult to locate relevant and useful informative blog as I found this one to get more knowledge but this is really a nice one.
vSan 6 for Remote Office Branch Office