R\u00e9cemment, j’ai eu besoin de transf\u00e9rer les logins depuis une instance SQL vers une autre.<\/p>\n
Je suis donc naturellement tomb\u00e9 sur la page suivante sur le site du support Microsoft : Comment faire pour transf\u00e9rer des noms d’acc\u00e8s et des mots de passe entre instances de SQL\u00a0Server<\/a><\/p>\n Mais le script fourni par Microsoft omet un point tr\u00e8s important.<\/p>\n En effet, le script pr\u00e9sent sur\u00a0la page du support fait son boulot, \u00e0 savoir qu’il permet depuis une instance SQL de g\u00e9n\u00e9rer un script permettant de recr\u00e9er les logins pr\u00e9sents, qu’il s’agisse d’identifiants SQL Server ou d’identifiants Windows.<\/p>\n Mais l\u00e0 ou les diff\u00e9rentes permissions niveau base de donn\u00e9es pourront \u00eatre reprises via des sauvegardes et restaurations des bases, il manque les droits niveau serveur pour ces diff\u00e9rents logins.<\/p>\n Voici donc une version du script de Microsoft compl\u00e9t\u00e9 afin de transf\u00e9rer aussi les r\u00f4les et permissions niveau serveur pour les diff\u00e9rents logins.<\/p>\n R\u00e9cemment, j’ai eu besoin de transf\u00e9rer les logins depuis une instance SQL vers une autre. Je suis donc naturellement tomb\u00e9 sur la page suivante sur le site du support Microsoft : Comment faire pour transf\u00e9rer des noms d’acc\u00e8s et des … Continuer la lecture USE tempdb\r\nGO\r\n\r\nIF OBJECT_ID ('dbo.sp_hexadecimal') IS NOT NULL\r\n DROP PROCEDURE dbo.sp_hexadecimal\r\nGO\r\n\r\nCREATE PROCEDURE dbo.sp_hexadecimal\r\n @binvalue varbinary(256),\r\n @hexvalue varchar (514) OUTPUT\r\nAS\r\n\tDECLARE @charvalue varchar (514)\r\n\tDECLARE @i int\r\n\tDECLARE @length int\r\n\tDECLARE @hexstring char(16)\r\n\tSELECT @charvalue = '0x'\r\n\tSELECT @i = 1\r\n\tSELECT @length = DATALENGTH (@binvalue)\r\n\tSELECT @hexstring = '0123456789ABCDEF'\r\n\r\n\tWHILE (@i <= @length)\r\n\tBEGIN\r\n\t DECLARE @tempint int\r\n\t DECLARE @firstint int\r\n\t DECLARE @secondint int\r\n\t SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))\r\n\t SELECT @firstint = FLOOR(@tempint\/16)\r\n\t SELECT @secondint = @tempint - (@firstint*16)\r\n\t SELECT @charvalue = @charvalue +\r\n\t\tSUBSTRING(@hexstring, @firstint+1, 1) +\r\n\t\tSUBSTRING(@hexstring, @secondint+1, 1)\r\n\t SELECT @i = @i + 1\r\n\tEND\r\n\tSELECT @hexvalue = @charvalue\r\nGO\r\n\r\nIF OBJECT_ID ('dbo.sp_help_revlogin') IS NOT NULL\r\n DROP PROCEDURE dbo.sp_help_revlogin\r\nGO\r\n\r\nCREATE PROCEDURE dbo.sp_help_revlogin @login_name sysname = NULL AS\r\n\tDECLARE @name sysname\r\n\tDECLARE @type varchar (1)\r\n\tDECLARE @hasaccess int\r\n\tDECLARE @denylogin int\r\n\tDECLARE @is_disabled int\r\n\tDECLARE @PWD_varbinary varbinary (256)\r\n\tDECLARE @PWD_string varchar (514)\r\n\tDECLARE @SID_varbinary varbinary (85)\r\n\tDECLARE @SID_string varchar (514)\r\n\tDECLARE @tmpstr varchar (1024)\r\n\tDECLARE @is_policy_checked varchar (3)\r\n\tDECLARE @is_expiration_checked varchar (3)\r\n\tDECLARE @defaultdb sysname\r\n\tDECLARE @defaultlanguage sysname\r\n\tIF (@login_name IS NULL)\r\n\t DECLARE login_curs CURSOR FOR\r\n\t\t SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name\r\n\t\t FROM sys.server_principals p\r\n\t\t\tLEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'\r\n\t\t ORDER BY p.principal_id\r\n\tELSE\r\n\t DECLARE login_curs CURSOR FOR\r\n\t\t SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name\r\n\t\t FROM sys.server_principals p\r\n\t\t LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name\r\n\r\n\tOPEN login_curs\r\n\tFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin,@defaultlanguage\r\n\r\n\tIF (@@fetch_status <> 0)\r\n\tBEGIN\r\n\t PRINT 'No login(s) found.'\r\n\t CLOSE login_curs\r\n\t DEALLOCATE login_curs\r\n\t RETURN -1\r\n\tEND\r\n\r\n\tSET @tmpstr = '\/* New logins script '\r\n\tPRINT @tmpstr\r\n\tSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' *\/'\r\n\tPRINT @tmpstr\r\n\tPRINT ''\r\n\r\n\tWHILE (@@fetch_status = 0)\r\n\tBEGIN\r\n\t PRINT ''\r\n\t SET @tmpstr = '-- Login: ' + @name\r\n\t PRINT @tmpstr\r\n\t SET @tmpstr = 'IF NOT EXISTS (SELECT * from master.sys.server_principals WHERE name=''' + replace (@name,'''','''''') + ''') BEGIN '\r\n\t IF (@type IN ( 'G', 'U'))\r\n\t BEGIN -- NT authenticated account\/group\r\n\t\t\t SET @tmpstr = @tmpstr + 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + ', DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)\r\n\t END\r\n\t ELSE BEGIN -- SQL Server authentication\r\n\t\t\t -- obtain password and sid\r\n\t\t\t SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )\r\n\t\t\t EXEC tempdb.dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT\r\n\t\t\t EXEC tempdb.dbo.sp_hexadecimal @SID_varbinary,@SID_string OUT\r\n\t\t\t -- obtain password policy state\r\n\t\t\t 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\r\n\t\t\t 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\r\n\t\t\t SET @tmpstr = @tmpstr + 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + ', DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)\r\n\t\t\t IF ( @is_policy_checked IS NOT NULL )\r\n\t\t\t BEGIN\r\n\t\t\t\t\t SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked\r\n\t\t\t END\r\n\t\t\t IF ( @is_expiration_checked IS NOT NULL )\r\n\t\t\t BEGIN\r\n\t\t\t\t\t SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked\r\n\t\t\t END\r\n\t END\r\n\t SET @tmpstr = @tmpstr + ' END'\r\n\t IF (@denylogin = 1)\r\n\t BEGIN -- login is denied access\r\n\t\t\t SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )\r\n\t END\r\n\t ELSE IF (@hasaccess = 0)\r\n\t BEGIN -- login exists but does not have access\r\n\t\t\t SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )\r\n\t END\r\n\t IF (@is_disabled = 1)\r\n\t BEGIN -- login is disabled\r\n\t\t\t SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'\r\n\t END\r\n\t -- Server Roles\r\n\t select @tmpstr=@tmpstr+'; EXEC master..sp_addsrvrolemember @loginame = ' + QUOTENAME( @name ) + ', @rolename = ' + QUOTENAME( r.name )\r\n\t from master.sys.server_role_members rm\r\n\t join master.sys.server_principals r on r.principal_id = rm.role_principal_id\r\n\t join master.sys.server_principals l on l.principal_id = rm.member_principal_id\r\n\t where l.[name] = @name\r\n\t -- GRANT\r\n\t select @tmpstr=@tmpstr+N';GRANT ' + per.permission_name + N' TO ' + QUOTENAME (pr.name) +\r\n\t case when per.state='W' then N' WITH GRANT OPTION' else N'' end +\r\n\t N' AS ' + QUOTENAME (gra.name) COLLATE Latin1_General_CI_AS_KS_WS\r\n\t from sys.server_permissions per\r\n\t join sys.server_principals AS pr on pr.principal_id=per.grantee_principal_id\r\n\t join sys.server_principals AS gra on gra.principal_id=per.grantor_principal_id\r\n\t where per.class_desc='SERVER' and per.state in ('G','W') and pr.name not like '##%'\r\n\t and pr.sid=@SID_varbinary\r\n\t -- DENY\r\n\t select @tmpstr=@tmpstr+N';DENY ' + per.permission_name + N' TO ' + QUOTENAME (pr.name) +\r\n\t N' AS ' + QUOTENAME (gra.name) COLLATE Latin1_General_CI_AS_KS_WS\r\n\t from sys.server_permissions per\r\n\t join sys.server_principals AS pr on pr.principal_id=per.grantee_principal_id\r\n\t join sys.server_principals AS gra on gra.principal_id=per.grantor_principal_id\r\n\t where per.class_desc='SERVER' and per.state ='D' and pr.name not like '##%'\r\n\t and pr.sid=@SID_varbinary\r\n\t PRINT @tmpstr\r\n\t FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage\r\n\t END\r\n\tCLOSE login_curs\r\n\tDEALLOCATE login_curs\r\n\tRETURN 0\r\nGO\r\n\r\nexec tempdb.dbo.sp_help_revlogin<\/pre>\n","protected":false},"excerpt":{"rendered":"