DQ NO.1 - dragon quest fans club

 找回密碼
 加入成為夥伴
搜索
熱搜: 活動 交友 discuz
查看: 1422|回復: 0

[SQL] 轉移 SQL Server 使用者帳號密碼

[複製鏈接]
發表於 2015-4-10 14:44:06 | 顯示全部樓層 |閱讀模式
How to transfer logins and passwords between instances of SQL Server
【如何在兩個SQL Server執行個體之間傳送登入帳號及密碼。】

參考來源:http://colinlinblog.pixnet.net/blog/post/127739117-%E2%98%85%E2%98%86%E2%98%86-%E5%8D%87%E7%B4%9A%E5%88%B0sql-server-2012---part-v---%E7%A7%BB%E8%BD%89%E4%BD%BF%E7%94%A8



伺服器A - 舊的伺服器
伺服器B - 新的伺服器(要匯入的伺服器)
做法
1.打開MSSMS(SQL Server Management Studio),連接到伺服器A,打開一個查詢視窗。
2.將以下這段script貼上,執行。
  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. IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  34. DROP PROCEDURE sp_help_revlogin
  35. GO
  36. CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
  37. DECLARE @name sysname
  38. DECLARE @type varchar (1)
  39. DECLARE @hasaccess int
  40. DECLARE @denylogin int
  41. DECLARE @is_disabled int
  42. DECLARE @PWD_varbinary  varbinary (256)
  43. DECLARE @PWD_string  varchar (514)
  44. DECLARE @SID_varbinary varbinary (85)
  45. DECLARE @SID_string varchar (514)
  46. DECLARE @tmpstr  varchar (1024)
  47. DECLARE @is_policy_checked varchar (3)
  48. DECLARE @is_expiration_checked varchar (3)

  49. DECLARE @defaultdb sysname

  50. IF (@login_name IS NULL)
  51. DECLARE login_curs CURSOR FOR

  52. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
  53. sys.server_principals p LEFT JOIN sys.syslogins l
  54. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
  55. ELSE
  56. DECLARE login_curs CURSOR FOR


  57. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
  58. sys.server_principals p LEFT JOIN sys.syslogins l
  59. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
  60. OPEN login_curs

  61. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
  62. IF (@@fetch_status = -1)
  63. BEGIN
  64. PRINT 'No login(s) found.'
  65. CLOSE login_curs
  66. DEALLOCATE login_curs
  67. RETURN -1
  68. END
  69. SET @tmpstr = '/* sp_help_revlogin script '
  70. PRINT @tmpstr
  71. SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
  72. PRINT @tmpstr
  73. PRINT ''
  74. WHILE (@@fetch_status <> -1)
  75. BEGIN
  76. IF (@@fetch_status <> -2)
  77. BEGIN
  78. PRINT ''
  79. SET @tmpstr = '-- Login:' + @name
  80. PRINT @tmpstr
  81. IF (@type IN ( 'G', 'U'))
  82. BEGIN -- NT authenticated account/group

  83. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
  84. END
  85. ELSE BEGIN -- SQL Server authentication
  86. -- obtain password and sid
  87. SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
  88. EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
  89. EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

  90. -- obtain password policy state
  91. 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
  92. 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

  93. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

  94. IF ( @is_policy_checked IS NOT NULL )
  95. BEGIN
  96. SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
  97. END
  98. IF ( @is_expiration_checked IS NOT NULL )
  99. BEGIN
  100. SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
  101. END
  102. END
  103. IF (@denylogin = 1)
  104. BEGIN -- login is denied access
  105. SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
  106. END
  107. ELSE IF (@hasaccess = 0)
  108. BEGIN -- login exists but does not have access
  109. SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
  110. END
  111. IF (@is_disabled = 1)
  112. BEGIN -- login is disabled
  113. SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
  114. END
  115. PRINT @tmpstr
  116. END

  117. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
  118. END
  119. CLOSE login_curs
  120. DEALLOCATE login_curs
  121. RETURN 0
  122. GO
複製代碼


3.執行上面這段代碼後,會建立兩個Store Procedure,sp_hexadecimal 與 sp_help_revlogin 。
4.執行預存程序 sp_help_revlogin
  1. EXEC sp_help_revlogin
複製代碼
5.預存程序執行後,會得到一段script,這段script可以建立登入者帳號密碼資訊。
6.在SSMS打開一個新的查詢視窗,連接到伺服器B,貼上剛剛執行 sp_help_revlogin 後獲得的Script,完成轉移。


您需要登錄後才可以回帖 登錄 | 加入成為夥伴

本版積分規則

Archiver|手機版|ぱふぱふ屋|DQ NO.1

GMT+8, 2019-1-21 20:21 , Processed in 0.048540 second(s), 16 queries .

Powered by Discuz! X3.4

© 2001-2017 Comsenz Inc.

快速回復 返回頂部 返回列表