Thursday, October 22, 2015

Work Adventure: Fix Orphaned user

When I restored a database to a new server, the users were imported but the login were not. The database users were  imported as the orphan users in SQL 2012

Here is how I found the orphaned users in a database

EXEC sp_change_users_login @Action='Report'

--OR we can run the following in the current database context

select * from sysusers where issqluser = 1 and (sid <> 0x0

and sid is not null)

and (LEN(sid) <= 16 ) AND suser_sname(sid) is null 





-----------------------------------------

The in order to link the orphan user to the existing login  I did the following


sp_change_users_login 'Update_One', 'OrphanUser, 'Logintolinkto'

 

No comments:

Post a Comment