Copie de logins

Publié le par

Récemment, j’ai eu besoin de transférer les logins depuis une instance SQL vers une autre.

Je suis donc naturellement tombé sur la page suivante sur le site du support Microsoft : Comment faire pour transférer des noms d’accès et des mots de passe entre instances de SQL Server

Mais le script fourni par Microsoft omet un point très important.

En effet, le script présent sur la page du support fait son boulot, à savoir qu’il permet depuis une instance SQL de générer un script permettant de recréer les logins présents, qu’il s’agisse d’identifiants SQL Server ou d’identifiants Windows.

Mais là ou les différentes permissions niveau base de données pourront être reprises via des sauvegardes et restaurations des bases, il manque les droits niveau serveur pour ces différents logins.

Voici donc une version du script de Microsoft complété afin de transférer aussi les rôles et permissions niveau serveur pour les différents logins.

USE tempdb
GO

IF OBJECT_ID ('dbo.sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE dbo.sp_hexadecimal
GO

CREATE PROCEDURE dbo.sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
	DECLARE @charvalue varchar (514)
	DECLARE @i int
	DECLARE @length int
	DECLARE @hexstring char(16)
	SELECT @charvalue = '0x'
	SELECT @i = 1
	SELECT @length = DATALENGTH (@binvalue)
	SELECT @hexstring = '0123456789ABCDEF'

	WHILE (@i <= @length)
	BEGIN
	  DECLARE @tempint int
	  DECLARE @firstint int
	  DECLARE @secondint int
	  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
	  SELECT @firstint = FLOOR(@tempint/16)
	  SELECT @secondint = @tempint - (@firstint*16)
	  SELECT @charvalue = @charvalue +
		SUBSTRING(@hexstring, @firstint+1, 1) +
		SUBSTRING(@hexstring, @secondint+1, 1)
	  SELECT @i = @i + 1
	END
	SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('dbo.sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE dbo.sp_help_revlogin
GO

CREATE PROCEDURE dbo.sp_help_revlogin @login_name sysname = NULL AS
	DECLARE @name sysname
	DECLARE @type varchar (1)
	DECLARE @hasaccess int
	DECLARE @denylogin int
	DECLARE @is_disabled int
	DECLARE @PWD_varbinary  varbinary (256)
	DECLARE @PWD_string  varchar (514)
	DECLARE @SID_varbinary varbinary (85)
	DECLARE @SID_string varchar (514)
	DECLARE @tmpstr  varchar (1024)
	DECLARE @is_policy_checked varchar (3)
	DECLARE @is_expiration_checked varchar (3)
	DECLARE @defaultdb sysname
	DECLARE @defaultlanguage sysname
	IF (@login_name IS NULL)
	  DECLARE login_curs CURSOR FOR
		  SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name
		  FROM sys.server_principals p
			LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
		  ORDER BY p.principal_id
	ELSE
	  DECLARE login_curs CURSOR FOR
		  SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name
		  FROM sys.server_principals p
		  LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

	OPEN login_curs
	FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin,@defaultlanguage

	IF (@@fetch_status <> 0)
	BEGIN
	  PRINT 'No login(s) found.'
	  CLOSE login_curs
	  DEALLOCATE login_curs
	  RETURN -1
	END

	SET @tmpstr = '/* New logins script '
	PRINT @tmpstr
	SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
	PRINT @tmpstr
	PRINT ''

	WHILE (@@fetch_status = 0)
	BEGIN
	   PRINT ''
	   SET @tmpstr = '-- Login: ' + @name
	   PRINT @tmpstr
	   SET @tmpstr = 'IF NOT EXISTS (SELECT * from master.sys.server_principals WHERE name=''' + replace (@name,'''','''''') + ''') BEGIN '
	   IF (@type IN ( 'G', 'U'))
	   BEGIN -- NT authenticated account/group
			  SET @tmpstr = @tmpstr + 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + ', DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
	   END
	   ELSE BEGIN -- SQL Server authentication
			  -- obtain password and sid
			  SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
			  EXEC tempdb.dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
			  EXEC tempdb.dbo.sp_hexadecimal @SID_varbinary,@SID_string OUT
			  -- obtain password policy state
			  SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
			  SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
			  SET @tmpstr = @tmpstr + 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + ', DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
			  IF ( @is_policy_checked IS NOT NULL )
			  BEGIN
					 SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
			  END
			  IF ( @is_expiration_checked IS NOT NULL )
			  BEGIN
					 SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
			  END
	   END
	   SET @tmpstr = @tmpstr + ' END'
	   IF (@denylogin = 1)
	   BEGIN -- login is denied access
			  SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
	   END
	   ELSE IF (@hasaccess = 0)
	   BEGIN -- login exists but does not have access
			  SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
	   END
	   IF (@is_disabled = 1)
	   BEGIN -- login is disabled
			  SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
	   END
	   -- Server Roles
	   select @tmpstr=@tmpstr+'; EXEC master..sp_addsrvrolemember @loginame = ' + QUOTENAME( @name ) + ', @rolename = ' + QUOTENAME( r.name )
	   from master.sys.server_role_members rm
	   join master.sys.server_principals r on r.principal_id = rm.role_principal_id
	   join master.sys.server_principals l on l.principal_id = rm.member_principal_id
	   where l.[name] = @name
	   -- GRANT
	   select @tmpstr=@tmpstr+N';GRANT ' + per.permission_name + N' TO ' + QUOTENAME (pr.name) +
	   case when per.state='W' then N' WITH GRANT OPTION' else N'' end +
	   N' AS ' + QUOTENAME (gra.name) COLLATE Latin1_General_CI_AS_KS_WS
	   from sys.server_permissions per
	   join sys.server_principals AS pr on pr.principal_id=per.grantee_principal_id
	   join sys.server_principals AS gra on gra.principal_id=per.grantor_principal_id
	   where per.class_desc='SERVER' and per.state in ('G','W') and pr.name not like '##%'
	   and pr.sid=@SID_varbinary
	   -- DENY
	   select @tmpstr=@tmpstr+N';DENY ' + per.permission_name + N' TO ' + QUOTENAME (pr.name) +
	   N' AS ' + QUOTENAME (gra.name) COLLATE Latin1_General_CI_AS_KS_WS
	   from sys.server_permissions per
	   join sys.server_principals AS pr on pr.principal_id=per.grantee_principal_id
	   join sys.server_principals AS gra on gra.principal_id=per.grantor_principal_id
	   where per.class_desc='SERVER' and per.state ='D' and pr.name not like '##%'
	   and pr.sid=@SID_varbinary
	   PRINT @tmpstr
	   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage
	   END
	CLOSE login_curs
	DEALLOCATE login_curs
	RETURN 0
GO

exec tempdb.dbo.sp_help_revlogin

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Etes-vous un robot ? *Chargement du capcha...

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.