Orphaned user is a common problem in SQL server world.When I was working on internal security audit of our production servers, I realized that sp_change_users_login with report option will not work with users associated with the windows login.
Who is an Orphan Users?
An user in a database is called orphaned when the associated login does not exists in the server or login has different SID. The first scenario can happen when you delete a login from the server. For example you created a login for an employee Mydomain\Lastname.Firstname and given access to couple of databases.Later this employee left the company or moved to different department.As a part of process we will delete his/her login (Mydomain\Lastname.Firstname) from the server. Now the databases on which he had access will have orphaned users.
The second scenario will happen when you restore the database from one environment (instance) to another environment(instance) or when you drop the login and create it again.For example you have login Mydomain\Lastname.Firstname in two instances namely INST1 and INST2. This login has db_owner right on one of the database (MyDb) in INST1. Now you have taken backup of MyDb and restored it on the INST2 and surprisingly Mydomain\Lastname.Firstname will not be able to access the restored database. If he/she tries to access the database , SQL server throw an error
Msg 916, Level 14, State 1, Line 1
The server principal "Mydomain\Lastname.Firstname" is not able to access the database "MyDb" under the current security context.
When you try to create a user on the restored database for Mydomain\Lastname.Firstname , it will throw an error as below
Msg 15023, Level 16, State 1, Line 1
User, group, or role '
Mydomain\Lastname.Firstname ' already exists in the current database.
This is happening because the SID of the login and user in the database is not matching. You can check this by examining the catalog views sys.server_principals and sys.database_principals.
Identifying the orphaned users
To keep the database environment clean and safe, it is important to delete/fix the orphan users. In general ,the orphaned users created by the first scenario(login deleted) should be deleted and orphaned users created by second scenario should be fixed. The easiest way to identify the orphaned users is , use the system procedure sp_change_users_login with report option as given below.
