{"id":968,"date":"2013-09-21T23:47:13","date_gmt":"2013-09-21T21:47:13","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=968"},"modified":"2026-09-30T16:25:43","modified_gmt":"2026-09-30T14:25:43","slug":"copie-de-logins","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/copie-de-logins\/","title":{"rendered":"Copie de logins"},"content":{"rendered":"<p>R\u00e9cemment, j&rsquo;ai eu besoin de transf\u00e9rer les logins depuis une instance SQL vers une autre.<\/p>\n<p>Je suis donc naturellement tomb\u00e9 sur la page suivante sur le site du support Microsoft : <a title=\"Transf\u00e9rer des noms d\u2019acc\u00e8s et des mots de passe entre des instances de SQL Server\" href=\"https:\/\/support.microsoft.com\/fr-fr\/help\/246133\/how-to-transfer-logins-and-passwords-between-instances-of-sql-server\" target=\"_blank\">Transf\u00e9rer des noms d\u2019acc\u00e8s et des mots de passe entre des instances de SQL Server<\/a><\/p>\n<p>Mais le script fourni par Microsoft omet un point tr\u00e8s important.<!--more--><\/p>\n<p>En effet, le script pr\u00e9sent sur\u00a0la page du support fait son boulot, \u00e0 savoir qu&rsquo;il permet depuis une instance SQL de g\u00e9n\u00e9rer un script permettant de recr\u00e9er les logins pr\u00e9sents, qu&rsquo;il s&rsquo;agisse d&rsquo;identifiants SQL Server ou d&rsquo;identifiants Windows.<\/p>\n<p>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<p>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<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">USE tempdb\nGO\n\nIF OBJECT_ID ('dbo.sp_hexadecimal') IS NOT NULL\n  DROP PROCEDURE dbo.sp_hexadecimal\nGO\n\nCREATE PROCEDURE dbo.sp_hexadecimal\n    @binvalue varbinary(256),\n    @hexvalue varchar (514) OUTPUT\nAS\n\tDECLARE @charvalue varchar (514)\n\tDECLARE @i int\n\tDECLARE @length int\n\tDECLARE @hexstring char(16)\n\tSELECT @charvalue = '0x'\n\tSELECT @i = 1\n\tSELECT @length = DATALENGTH (@binvalue)\n\tSELECT @hexstring = '0123456789ABCDEF'\n\n\tWHILE (@i &lt;= @length)\n\tBEGIN\n\t  DECLARE @tempint int\n\t  DECLARE @firstint int\n\t  DECLARE @secondint int\n\t  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))\n\t  SELECT @firstint = FLOOR(@tempint\/16)\n\t  SELECT @secondint = @tempint - (@firstint*16)\n\t  SELECT @charvalue = @charvalue +\n\t\tSUBSTRING(@hexstring, @firstint+1, 1) +\n\t\tSUBSTRING(@hexstring, @secondint+1, 1)\n\t  SELECT @i = @i + 1\n\tEND\n\tSELECT @hexvalue = @charvalue\nGO\n\nIF OBJECT_ID ('dbo.sp_help_revlogin') IS NOT NULL\n  DROP PROCEDURE dbo.sp_help_revlogin\nGO\n\nCREATE PROCEDURE dbo.sp_help_revlogin @login_name sysname = NULL AS\n\tDECLARE @name sysname\n\tDECLARE @type varchar (1)\n\tDECLARE @hasaccess int\n\tDECLARE @denylogin int\n\tDECLARE @is_disabled int\n\tDECLARE @PWD_varbinary  varbinary (256)\n\tDECLARE @PWD_string  varchar (514)\n\tDECLARE @SID_varbinary varbinary (85)\n\tDECLARE @SID_string varchar (514)\n\tDECLARE @tmpstr  varchar (1024)\n\tDECLARE @is_policy_checked varchar (3)\n\tDECLARE @is_expiration_checked varchar (3)\n\tDECLARE @defaultdb sysname\n\tDECLARE @defaultlanguage sysname\n\tIF (@login_name IS NULL)\n\t  DECLARE login_curs CURSOR FOR\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\n\t\t  FROM sys.server_principals p\n\t\t\tLEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name &lt;&gt; 'sa'\n\t\t  ORDER BY p.principal_id\n\tELSE\n\t  DECLARE login_curs CURSOR FOR\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\n\t\t  FROM sys.server_principals p\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\n\n\tOPEN login_curs\n\tFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin,@defaultlanguage\n\n\tIF (@@fetch_status &lt;&gt; 0)\n\tBEGIN\n\t  PRINT 'No login(s) found.'\n\t  CLOSE login_curs\n\t  DEALLOCATE login_curs\n\t  RETURN -1\n\tEND\n\n\tSET @tmpstr = '\/* New logins script '\n\tPRINT @tmpstr\n\tSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' *\/'\n\tPRINT @tmpstr\n\tPRINT ''\n\n\tWHILE (@@fetch_status = 0)\n\tBEGIN\n\t   PRINT ''\n\t   SET @tmpstr = '-- Login: ' + @name\n\t   PRINT @tmpstr\n\t   SET @tmpstr = 'IF NOT EXISTS (SELECT * from master.sys.server_principals WHERE name=''' + replace (@name,'''','''''') + ''') BEGIN '\n\t   IF (@type IN ( 'G', 'U'))\n\t   BEGIN -- NT authenticated account\/group\n\t\t\t  SET @tmpstr = @tmpstr + 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + ', DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)\n\t   END\n\t   ELSE BEGIN -- SQL Server authentication\n\t\t\t  -- obtain password and sid\n\t\t\t  SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )\n\t\t\t  EXEC tempdb.dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT\n\t\t\t  EXEC tempdb.dbo.sp_hexadecimal @SID_varbinary,@SID_string OUT\n\t\t\t  -- obtain password policy state\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\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\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)\n\t\t\t  IF ( @is_policy_checked IS NOT NULL )\n\t\t\t  BEGIN\n\t\t\t\t\t SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked\n\t\t\t  END\n\t\t\t  IF ( @is_expiration_checked IS NOT NULL )\n\t\t\t  BEGIN\n\t\t\t\t\t SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked\n\t\t\t  END\n\t   END\n\t   SET @tmpstr = @tmpstr + ' END'\n\t   IF (@denylogin = 1)\n\t   BEGIN -- login is denied access\n\t\t\t  SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )\n\t   END\n\t   ELSE IF (@hasaccess = 0)\n\t   BEGIN -- login exists but does not have access\n\t\t\t  SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )\n\t   END\n\t   IF (@is_disabled = 1)\n\t   BEGIN -- login is disabled\n\t\t\t  SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'\n\t   END\n\t   -- Server Roles\n\t   select @tmpstr=@tmpstr+'; EXEC master..sp_addsrvrolemember @loginame = ' + QUOTENAME( @name ) + ', @rolename = ' + QUOTENAME( r.name )\n\t   from master.sys.server_role_members rm\n\t   join master.sys.server_principals r on r.principal_id = rm.role_principal_id\n\t   join master.sys.server_principals l on l.principal_id = rm.member_principal_id\n\t   where l.[name] = @name\n\t   -- GRANT\n\t   select @tmpstr=@tmpstr+N';GRANT ' + per.permission_name + N' TO ' + QUOTENAME (pr.name) +\n\t   case when per.state='W' then N' WITH GRANT OPTION' else N'' end +\n\t   N' AS ' + QUOTENAME (gra.name) COLLATE Latin1_General_CI_AS_KS_WS\n\t   from sys.server_permissions per\n\t   join sys.server_principals AS pr on pr.principal_id=per.grantee_principal_id\n\t   join sys.server_principals AS gra on gra.principal_id=per.grantor_principal_id\n\t   where per.class_desc='SERVER' and per.state in ('G','W') and pr.name not like '##%'\n\t   and pr.sid=@SID_varbinary\n\t   -- DENY\n\t   select @tmpstr=@tmpstr+N';DENY ' + per.permission_name + N' TO ' + QUOTENAME (pr.name) +\n\t   N' AS ' + QUOTENAME (gra.name) COLLATE Latin1_General_CI_AS_KS_WS\n\t   from sys.server_permissions per\n\t   join sys.server_principals AS pr on pr.principal_id=per.grantee_principal_id\n\t   join sys.server_principals AS gra on gra.principal_id=per.grantor_principal_id\n\t   where per.class_desc='SERVER' and per.state ='D' and pr.name not like '##%'\n\t   and pr.sid=@SID_varbinary\n\t   PRINT @tmpstr\n\t   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage\n\t   END\n\tCLOSE login_curs\n\tDEALLOCATE login_curs\n\tRETURN 0\nGO\n\nexec tempdb.dbo.sp_help_revlogin<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>R\u00e9cemment, j&rsquo;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 : Transf\u00e9rer des noms d\u2019acc\u00e8s et des mots de passe &hellip; <a href=\"https:\/\/www.sqlserver.fr\/blog\/copie-de-logins\/\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-968","post","type-post","status-publish","format-standard","hentry","category-outils"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/968","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=968"}],"version-history":[{"count":10,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"predecessor-version":[{"id":1990,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/968\/revisions\/1990"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}