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