Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Migrate Logins between SQL Servers

From Wiki

Jump to: navigation, search

How to Mirgrate logins from one server to another.

Here are a couple of scripts that will help you move logins from one server to another. These scripts will create logins scripts with the same SIDS. This is very important if you need to restore databases from a prod server to a test server. You won't have to run sp_change_users_login to sync up the SIDS anymore.

Use this for SQL 2000.

  1. USE master
  2. GO
  3. IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  4.   DROP PROCEDURE sp_hexadecimal
  5. GO
  6. CREATE PROCEDURE sp_hexadecimal
  7.     @binvalue varbinary(256),
  8.     @hexvalue varchar(256) OUTPUT
  9. AS
  10. DECLARE @charvalue varchar(256)
  11. DECLARE @i int
  12. DECLARE @length int
  13. DECLARE @hexstring char(16)
  14. SELECT @charvalue = '0x'
  15. SELECT @i = 1
  16. SELECT @length = DATALENGTH (@binvalue)
  17. SELECT @hexstring = '0123456789ABCDEF'
  18. WHILE (@i <= @length)
  19. BEGIN
  20.   DECLARE @tempint int
  21.   DECLARE @firstint int
  22.   DECLARE @secondint int
  23.   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  24.   SELECT @firstint = FLOOR(@tempint/16)
  25.   SELECT @secondint = @tempint - (@firstint*16)
  26.   SELECT @charvalue = @charvalue +
  27.     SUBSTRING(@hexstring, @firstint+1, 1) +
  28.     SUBSTRING(@hexstring, @secondint+1, 1)
  29.   SELECT @i = @i + 1
  30. END
  31. SELECT @hexvalue = @charvalue
  32. GO
  33.  
  34. USE master
  35. GO
  36. CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
  37. DECLARE @name    sysname
  38. DECLARE @xstatus int
  39. DECLARE @binpwd  varbinary (256)
  40. DECLARE @txtpwd  sysname
  41. DECLARE @tmpstr  varchar (256)
  42. DECLARE @SID_varbinary varbinary(85)
  43. DECLARE @SID_string varchar(256)
  44.  
  45. IF (@login_name IS NULL)
  46.   DECLARE login_curs CURSOR FOR
  47.     SELECT sid, name, xstatus, password FROM master..sysxlogins
  48.     WHERE srvid IS NULL AND name <> 'sa'
  49. ELSE
  50.   DECLARE login_curs CURSOR FOR
  51.     SELECT sid, name, xstatus, password FROM master..sysxlogins
  52.     WHERE srvid IS NULL AND name = @login_name
  53. OPEN login_curs
  54. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  55. IF (@@fetch_status = -1)
  56. BEGIN
  57.   PRINT 'No login(s) found.'
  58.   CLOSE login_curs
  59.   DEALLOCATE login_curs
  60.   RETURN -1
  61. END
  62. SET @tmpstr = '/* sp_help_revlogin script '
  63. PRINT @tmpstr
  64. SET @tmpstr = '** Generated '
  65.   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
  66. PRINT @tmpstr
  67. PRINT ''
  68. PRINT 'DECLARE @pwd sysname'
  69. WHILE (@@fetch_status <> -1)
  70. BEGIN
  71.   IF (@@fetch_status <> -2)
  72.   BEGIN
  73.     PRINT ''
  74.     SET @tmpstr = '-- Login: ' + @name
  75.     PRINT @tmpstr
  76.     IF (@xstatus & 4) = 4
  77.     BEGIN -- NT authenticated account/group
  78.       IF (@xstatus & 1) = 1
  79.       BEGIN -- NT login is denied access
  80.         SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
  81.         PRINT @tmpstr
  82.       END
  83.       ELSE BEGIN -- NT login has access
  84.         SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
  85.         PRINT @tmpstr
  86.       END
  87.     END
  88.     ELSE BEGIN -- SQL Server authentication
  89.       IF (@binpwd IS NOT NULL)
  90.       BEGIN -- Non-null password
  91.         EXEC sp_hexadecimal @binpwd, @txtpwd OUT
  92.         IF (@xstatus & 2048) = 2048
  93.           SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
  94.         ELSE
  95.           SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
  96.         PRINT @tmpstr
  97.     EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  98.         SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
  99.           + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
  100.       END
  101.       ELSE BEGIN
  102.         -- Null password
  103.     EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  104.         SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
  105.           + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
  106.       END
  107.       IF (@xstatus & 2048) = 2048
  108.         -- login upgraded from 6.5
  109.         SET @tmpstr = @tmpstr + '''skip_encryption_old'''
  110.       ELSE
  111.         SET @tmpstr = @tmpstr + '''skip_encryption'''
  112.       PRINT @tmpstr
  113.     END
  114.   END
  115.   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  116.   END
  117. CLOSE login_curs
  118. DEALLOCATE login_curs
  119. RETURN 0
  120.  
  121. GO
  122.  
  123.  
  124.  ----- End Script -----


exec sp_help_revlogin


Use this version for 2005


  1. USE master
  2. GO
  3. IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  4.   DROP PROCEDURE sp_hexadecimal
  5. GO
  6. CREATE PROCEDURE sp_hexadecimal
  7.     @binvalue varbinary(256),
  8.     @hexvalue varchar (514) OUTPUT
  9. AS
  10. DECLARE @charvalue varchar (514)
  11. DECLARE @i int
  12. DECLARE @length int
  13. DECLARE @hexstring char(16)
  14. SELECT @charvalue = '0x'
  15. SELECT @i = 1
  16. SELECT @length = DATALENGTH (@binvalue)
  17. SELECT @hexstring = '0123456789ABCDEF'
  18. WHILE (@i <= @length)
  19. BEGIN
  20.   DECLARE @tempint int
  21.   DECLARE @firstint int
  22.   DECLARE @secondint int
  23.   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  24.   SELECT @firstint = FLOOR(@tempint/16)
  25.   SELECT @secondint = @tempint - (@firstint*16)
  26.   SELECT @charvalue = @charvalue +
  27.     SUBSTRING(@hexstring, @firstint+1, 1) +
  28.     SUBSTRING(@hexstring, @secondint+1, 1)
  29.   SELECT @i = @i + 1
  30. END
  31. SELECT @hexvalue = @charvalue
  32. GO
  33.  
  34.  
  35. IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  36.   DROP PROCEDURE sp_help_revlogin
  37. GO
  38. CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
  39. DECLARE @name sysname
  40. DECLARE @type varchar (1)
  41. DECLARE @hasaccess int
  42. DECLARE @denylogin int
  43. DECLARE @is_disabled int
  44. DECLARE @PWD_varbinary  varbinary (256)
  45. DECLARE @PWD_string  varchar (514)
  46. DECLARE @SID_varbinary varbinary (85)
  47. DECLARE @SID_string varchar (514)
  48. DECLARE @tmpstr  varchar (1024)
  49. DECLARE @is_policy_checked varchar (3)
  50. DECLARE @is_expiration_checked varchar (3)
  51.  
  52. IF (@login_name IS NULL)
  53.   DECLARE login_curs CURSOR FOR
  54.       SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
  55.         FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
  56.         WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
  57. ELSE
  58.   DECLARE login_curs CURSOR FOR
  59.       SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
  60.         FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
  61.         WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
  62. OPEN login_curs
  63. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
  64. IF (@@fetch_status = -1)
  65. BEGIN
  66.   PRINT 'No login(s) found.'
  67.   CLOSE login_curs
  68.   DEALLOCATE login_curs
  69.   RETURN -1
  70. END
  71. SET @tmpstr = '/* sp_help_revlogin script '
  72. PRINT @tmpstr
  73. SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
  74. PRINT @tmpstr
  75. PRINT ''
  76. WHILE (@@fetch_status <> -1)
  77. BEGIN
  78.   IF (@@fetch_status <> -2)
  79.   BEGIN
  80.     PRINT ''
  81.     SET @tmpstr = '-- Login: ' + @name
  82.     PRINT @tmpstr
  83.  
  84.     IF (@type IN ( 'G', 'U'))
  85.     BEGIN -- NT authenticated account/group
  86.       SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
  87.     END
  88.     ELSE BEGIN -- SQL Server authentication
  89.         -- obtain password and sid
  90.         SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
  91.         EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
  92.         EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
  93.  
  94.         -- obtain password policy state
  95.         SELECT @is_policy_checked =
  96.             CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
  97.             FROM sys.sql_logins WHERE name = @name
  98.         SELECT @is_expiration_checked =
  99.             CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
  100.             FROM sys.sql_logins WHERE name = @name
  101.  
  102.         SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )
  103.             + ' WITH PASSWORD = ' + @PWD_string
  104.             + ' HASHED, SID = ' + @SID_string
  105.  
  106.         IF ( @is_policy_checked IS NOT NULL )
  107.         BEGIN
  108.           SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
  109.         END
  110.         IF ( @is_expiration_checked IS NOT NULL )
  111.         BEGIN
  112.           SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
  113.         END
  114.     END
  115.  
  116.     IF (@denylogin = 1)
  117.     BEGIN -- login is denied access
  118.       SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
  119.     END
  120.     ELSE IF (@hasaccess = 0)
  121.     BEGIN -- login has exists but does not have access
  122.       SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
  123.     END
  124.  
  125.     IF (@is_disabled = 1)
  126.     BEGIN -- login is disabled
  127.       SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
  128.     END
  129.  
  130.     PRINT @tmpstr
  131.   END
  132.   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
  133.   END
  134. CLOSE login_curs
  135. DEALLOCATE login_curs
  136.  
  137. RETURN 0
  138. GO

Part of SQL Server Admin Hacks

403 Rating: 2.4/5 (42 votes cast)