How to Find and Fix Orphaned Users in SQL Server

How can we help you today?

Issue
When migrating or moving a database from one SQL Server instance to another SQL Server instance, orphan user(s) could be created.

Product
Microsoft SQL Server

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

Cause
When a database is moved or migrated, the database users in the migrated or moved database are not mapped to any
particular SQL Server login within the new SQL Server instance. A database user for which the corresponding SQL Server
login is undefined or is incorrectly defined on a server instance cannot log in to the instance. A database user not mapped
to SQL Server Login is referred to as an Orphan user.

Resolution

*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Below are some of the scripts to identify and fix the Orphan user issue.

Script for finding list of Orphan USERS for a particular database:

select db_name() as DBName,* from sys.database_principals

where sid not in (select sid from master.sys.server_principals)

AND type_desc != 'DATABASE_ROLE' AND name != 'guest'

Script for finding list of Orphan USERS from all the databases:

exec sp_msforeachdb ' use ?

select db_name() as DBName,* from sys.database_principals

where sid not in (select sid from master.sys.server_principals)

AND type_desc != ''DATABASE_ROLE'' AND name != ''guest'' '

Script to map a particular database USER with a LOGIN:

ALTER USER userName WITH LOGIN = loginName

Script to generate Alter User script which can be used to map all orphan USERS with LOGINS in a particular database:

select 'Alter User ' + name + ' WITH LOGIN = ' + name from sys.database_principals

where sid not in (select sid from master.sys.server_principals)

AND type_desc != 'DATABASE_ROLE' AND name != 'guest'

Note: These Alter commands will fail, if loginname with same name as username is not already present as a LOGIN, so first create the LOGIN if not already present.

Script to generate Alter USER script which can be used to map all orphan users with logins in all databases:

exec sp_msforeachdb ' use ?

select ''Alter User '' + name + '' WITH LOGIN = '' + name from sys.database_principals

where sid not in (select sid from master.sys.server_principals)

AND type_desc != ''DATABASE_ROLE'' AND name != ''guest'' '

Note:These Alter commands need to be run on their specific databases and Alter commands can fail, if loginname with same name as username is not already present as a LOGIN, so first create the LOGIN if not already present.

** see attachment containing the SQL script text