How to identify and fix the Orphaned Users in SQL Server?

Who are Orphan User?

An user in a database is called orphaned when the associated login does not exists in the server or 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.

Scenarios

1. When you delete a login from the server.
2. When you restore the database from one environment (production instance) to  another (development instance).

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.

EXEC SP_CHANGE_USER_LOGIN 'Report'

But interestingly, this procedure will list only the orphaned users associated with the SQL login. If the login associated with windows login, the above procedure will not return the desired result.

Below script will list the orphaned users from the all databases of an instance.

DECLARE @statement1 VARCHAR(MAX)
DECLARE @statement2 VARCHAR(MAX)
IF EXISTS (SELECT object_id FROM tempdb.sys.objects WHERE name like '#OUsers%')

DROP TABLE #OUsers
CREATE TABLE #OUsers
      (
 UserName VARCHAR(50) NULL,
        userSID  VARBINARY(85) NULL,
        dbName  VARCHAR(50) NULL       
      )  ON [PRIMARY]
SET @statement2 = ' sp_change_users_login ''''Report'''''
SET @statement1 = 'USE [?] 
    INSERT INTO #OUsers(UserName,userSID)
    exec ('''+@statement2+''')
    UPDATE #OUsers SET dbName = ''?''
    WHERE DBName is NUll'
EXEC sp_MSforeachdb @command1=@statement1

SET @statement1 ='USE [?] ;
insert into #OUsers
select name ,sid,''?'' from sys.database_principals where sid not in 
(select sid from sys.server_principals ) and type<>''r''
and type_desc=''WINDOWS_USER'''
EXEC sp_MSforeachdb @command1=@statement1

select * from #OUsers

Fixing the Orphaned users

In the scenario where the login is got deleted , the orphan users can be fixed by dropping the user from the databases using the below script.

DROP USER <username>

Some time the above script may throw an error saying that the users owns a schema. Find out the schema owned by this login by querying the catalog view sys.schemas and mapping the principal_id to sys.database_principals. Either drop these schemas or change the ownership based on your environment. To change the ownership of the schema, use the below command

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO NewUserName;

In the scenario where  database restored in a different environment, we can fix the SID mismatch between the sys.server_principals and sys.database_principals by using the system stored procedure sp_change_users_login. 

If need to link the SIDs of the users to the SIDs of the valid logins in the master database

Exec sp_change_users_login 'update_one', 'username', 'loginname'

Or if you are sure that SQL Server login name is the same as the orphaned user of database being mapped, then you may use a shorter command such as this for username.

EXEC sp_change_users_login 'Auto_Fix', 'username'

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

EXEC sp_change_users_login 'Auto_Fix', 'username', null,'pwd'

Note: This is a deprecated feature in SQL server 2008 and you can use alter user as given below

You need to first add the login using the below command 

sp_addlogin 'loginname', 'password'

then associate the user with the login you the command.

ALTER USER TestWindowsUser WITH LOGIN [Mydomain\Lastname.Firstname]

This will resolve the Orphan Users issues.