Sql Server: How to fix orphan users (and map them to logins)
- Posted by Sqltimes
- On February 14, 2015
- 0 Comments
Quick one today:
When I restore databases from one instance to a different instance, i.e. from QA environment to Integration environment or to UAT, this issue of orphan users occurs.
Login are instance level principals and Users are database level principals. Each user account at the database level is mapped to a login at instance level. As part of this mapping, sid (security ID) of the login is mapped to
|
1
2
3
4
5
6
7
8
9
10
11
12
|
---- Gather details of login and users--SELECT uid, name, sidFROM sys.sysusersWHERE name = 'Copient_Logix'GOSELECT name, sidFROM master.sys.sysloginsWHERE name = 'Copient_Logix'GO |
Login and User SID mapping
So, when you restore a database from one instance to another instance, this sid mapping will be incorrect; Resulting in orphan users at the database level. To correct this, we could take these steps.
Step 1:
Get a list of users that are orphans.
|
1
2
3
4
5
|
---- Gather list of orphan users--EXEC sp_change_users_login 'Report'GO |
Step 2:
|
1
2
3
4
5
|
---- Fix the orphan user--EXEC sp_change_users_login 'Auto_Fix', 'user'GO |
Hope this helps,
_Sqltimes
_Sqltimes
0 Comments