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
go
sp_change_users_login 'Update_One', 'MyAppUser', 'MyAppUser'

No comments: