My Technical Notes

Tuesday, 9 April 2013

Fixing Orphaned Users In SQL Server after a Database Restore

After restoring the database, we notice that the user exists but it is not attached to a login name. Therefore the first step is to create the database login. The second step is to run the following snippet of SQL:

EXEC sp_change_users_login 'UPDATE_ONE','userName','loginName'
--  the convention (sometimes) is that the userName and the loginName 
--  are the same.

However, the permissions of the user of the database is affected by the Server Roles of the Login - so we need to know how this was created.

No comments: