sp_change_users_login : Utilisateurs orphelins

15 novembre 2016

Commentaires fermés

En cas de restauration d’une base de données sur une autre instance SQL Server et bien que les logins soient présent sur celle-ci, il est impossible de se connecter sur la base de données restaurée, on obtient le message d’erreur suivant : Login failed. Le premier réflexe est de supprimer le login au niveau de l’instance et de le recréer mais au moment de l’attribution des droits sur la base de données impactée, on obtient le message d’erreur suivant : User already exists in current database. Mais que se passe t-il ? Il s’agit simplement d’une désynchronisation des SID entre les logins au niveau de l’instance et les users au niveau de la base de données restaurée (droits). Pour remédier à ce problème, utiliser le contexte de la base de données restaurée et exécuter l’instruction suivante :

USE [DatabaseName] 
GO 
 
DECLARE @SqlDyn VARCHAR(MAX) 

SELECT	@SQLDyn =  
CAST( 
		(  
			SELECT	'EXEC sp_change_users_login @Action=''Auto_Fix'', @UserNamePattern=''' + u.name + ''';' + CHAR(13) 
			FROM	sys.sysusers u 
			inner join sys.server_principals sp on sp.name COLLATE SQL_Latin1_General_CP1_CI_AS = u.name COLLATE SQL_Latin1_General_CP1_CI_AS 
			WHERE	u.issqluser = 1  
			AND	u.sid is not NULL 
			AND	len(u.sid) <= 16 
			AND	suser_sname(u.sid) is null 
			AND	sp.type not in ('C', 'K') 
			FOR XML PATH(''), root('dynsql'), type 
		).value('/dynsql[1]', 'varchar(8000)') 
AS VARCHAR(8000)) 

PRINT @SqlDyn
EXEC (@SqlDyn)

USE [DatabaseName]

DECLARE @SqlDyn VARCHAR(MAX)

SELECT @SQLDyn =

CAST(

(

SELECT 'EXEC sp_change_users_login @Action=''Auto_Fix'', @UserNamePattern=''' + u.name + ''';' + CHAR(13)

FROM sys.sysusers u

inner join sys.server_principals sp on sp.name COLLATE SQL_Latin1_General_CP1_CI_AS = u.name COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE u.issqluser = 1

AND u.sid is not NULL

AND len(u.sid) <= 16

AND suser_sname(u.sid) is null

AND sp.type not in ('C', 'K')

FOR XML PATH(''), root('dynsql'), type

).value('/dynsql[1]', 'varchar(8000)')

AS VARCHAR(8000))

PRINT @SqlDyn

EXEC (@SqlDyn)

Cette procédure permet d’effectuer un mapping entre les logins et users de la base de données restaurée.

Pour appliquer cette procédure sur toutes les bases de données, soit l’intégralité de l’instance, exécuter cette requête :

DECLARE @SqlExec VARCHAR(MAX)

SET @SqlExec = '
USE [?]; 

SET QUOTED_IDENTIFIER ON

DECLARE @SqlDyn VARCHAR(MAX)

SELECT	@SQLDyn = CAST(

				( 

					SELECT	''EXEC sp_change_users_login @Action=''''Auto_Fix'''', @UserNamePattern='''''' + u.name + '''''';'' +  CHAR(10)  AS [text()]
					FROM	sys.sysusers u
					inner join sys.server_principals sp on sp.name COLLATE SQL_Latin1_General_CP1_CI_AS = u.name COLLATE SQL_Latin1_General_CP1_CI_AS
					WHERE	u.issqluser = 1 
					AND		u.sid is not NULL
					AND		len(u.sid) <= 16
					AND		suser_sname(u.sid) is null
					AND		sp.type not in (''C'', ''K'')
					FOR XML PATH(''''), root(''dynsql''), type 
							).value(''/dynsql[1]'', ''varchar(8000)'') 
			AS VARCHAR(8000)) 

PRINT @SqlDyn
EXEC (@SqlDyn)
'

PRINT @SqlExec

EXEC sp_MSforeachdb @SqlExec

DECLARE @SqlExec VARCHAR(MAX)

SET @SqlExec = '

USE [?];

SET QUOTED_IDENTIFIER ON

DECLARE @SqlDyn VARCHAR(MAX)

SELECT @SQLDyn = CAST(

(

SELECT ''EXEC sp_change_users_login @Action=''''Auto_Fix'''', @UserNamePattern='''''' + u.name + '''''';'' + CHAR(10) AS [text()]

FROM sys.sysusers u

inner join sys.server_principals sp on sp.name COLLATE SQL_Latin1_General_CP1_CI_AS = u.name COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE u.issqluser = 1

AND u.sid is not NULL

AND len(u.sid) <= 16

AND suser_sname(u.sid) is null

AND sp.type not in (''C'', ''K'')

FOR XML PATH(''''), root(''dynsql''), type

).value(''/dynsql[1]'', ''varchar(8000)'')

AS VARCHAR(8000))

PRINT @SqlDyn

EXEC (@SqlDyn)

PRINT @SqlExec

EXEC sp_MSforeachdb @SqlExec

Cela étant, on peut très bien s’affranchir du mapping en effectuant une copie des logins sur l’instance cible comprenant les SID et mot de passe de la source, voir :

https://www.concatskills.com/2016/08/15/copie-logins-dune-instance-a-lautre/