Recently I had to deal with what we call SQL Server 'Orphaned Users'. In this post I will talk about what they are and how to fix them. In any database environment, we need to create a Database User with specific privileges (please see my other article on creating a limited SQL Server User). However when we restore the database on another machine, we end up having 'Orphaned Users'.
An orphaned user is the one without matching SID. As you are aware, every object in SQL Server including Users have an SID which uniquely identifies it. This SID is basically a GUID stored in the Master Database. When we restore a database on another machine, the restored User has the SID from the previous location. This doesn't exist on the new machine. Even if we create a Login and associate it with the User, both SIDs are different. This results in Orphaned Users without matching IDs. To fix the above issue, we use the sp_change_users_login stored procedure. This SP has different parameters for different purposes.
To report any orphaned user, the following synxtax is used:
EXEC sp_change_users_login 'REPORT'
Similarly, if the target machine already has a Login with the same name (but different SID), the following will associate the respective User and Login:
EXEC sp_change_users_login 'Auto_Fix', 'Employee'
The above will associate the 'Employee' User with a Login of the same name. Similarly, if we first want to create the Login and then associate it with the respective User, the following synxtax will be used:
EXEC sp_change_users_login 'Auto_Fix', 'Employee', null, 'pwd'
The above will first create a User 'Employee' with Password 'Pwd' and then associate it with the respective User. We can also use the Update_One parameter which will associate a User to an existing Login.
I hope you have learned from this blog. Stay tunned for more...
An orphaned user is the one without matching SID. As you are aware, every object in SQL Server including Users have an SID which uniquely identifies it. This SID is basically a GUID stored in the Master Database. When we restore a database on another machine, the restored User has the SID from the previous location. This doesn't exist on the new machine. Even if we create a Login and associate it with the User, both SIDs are different. This results in Orphaned Users without matching IDs. To fix the above issue, we use the sp_change_users_login stored procedure. This SP has different parameters for different purposes.
To report any orphaned user, the following synxtax is used:
EXEC sp_change_users_login 'REPORT'
Similarly, if the target machine already has a Login with the same name (but different SID), the following will associate the respective User and Login:
EXEC sp_change_users_login 'Auto_Fix', 'Employee'
The above will associate the 'Employee' User with a Login of the same name. Similarly, if we first want to create the Login and then associate it with the respective User, the following synxtax will be used:
EXEC sp_change_users_login 'Auto_Fix', 'Employee', null, 'pwd'
The above will first create a User 'Employee' with Password 'Pwd' and then associate it with the respective User. We can also use the Update_One parameter which will associate a User to an existing Login.
I hope you have learned from this blog. Stay tunned for more...
