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
Publicado para: Schneider Electric España
¿Necesitas ayuda?
Empieza aquí
Encuentra respuestas ahora. Busca una solución por ti mismo, o ponte en contacto con uno de nuestros expertos.
Contacta con el servicio de asistencia
Ponte en contacto con nuestro equipo de atención al cliente para recibir información, soporte técnico, asistencia para reclamaciones y mucho más.
¿Dónde comprar?
Encuentra fácilmente el distribuidor de Schneider Electric más cercano a tu ubicación.
Buscar preguntas frecuentes
Obtén las respuestas que necesitas examinando las preguntas frecuentes (FAQ) relacionadas con los temas.
Contacto con el servicio de ventas
Comienza tu consulta de ventas on-line y un experto se pondrá en contacto contigo.
