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:
Script for finding list of Orphan USERS from all the databases:
Script to map a particular database USER with a LOGIN:
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'' 'ALTER USER userName WITH LOGIN = loginNameScript to generate Alter User script which can be used to map all orphan USERS with LOGINS in a particular database
Note:
: 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:
** see attachment containing the SQL script text
