Dala ruma ita hakarak usa base de daus SQL server husi production ba iha test server maibe dalabarak mos ita infrenta problema ho security login, ho rasaun ida ne’e ita tenke import user credencial husi ne’ebe ita exporta base de dadus.
Pasu por pasu tuir mai ne’e lori ita ba hetan resultadu saida mak ita hakarak.
Atu kria login script ne’ebe nia password enkript hanesan tuir mai:
- Iha server A, loke SQL Server Management Studio, no halo koneksaun ba iha instancia SQL server ne’ebe ita hakarak duplika nia login kredencial.
- Loke editorial Query foun ida no halao script tuir mai ne’e.
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.
32. SELECT @hexvalue = @charvalue
33. GO
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. DECLARE @defaultdb sysname
53.
54. IF (@login_name IS NULL)
55. DECLARE login_curs CURSOR FOR
56.
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 <> 'sa'
60. ELSE
61. DECLARE login_curs CURSOR FOR
62.
63.
64. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
65. sys.server_principals p LEFT JOIN sys.syslogins l
66. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
67. OPEN login_curs
68.
69. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
70. IF (@@fetch_status = -1)
71. BEGIN
72. PRINT 'No login(s) found.'
73. CLOSE login_curs
74. DEALLOCATE login_curs
75. RETURN -1
76. END
77. SET @tmpstr = '/* sp_help_revlogin script '
78. PRINT @tmpstr
79. SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
80. PRINT @tmpstr
81. PRINT ''
82. WHILE (@@fetch_status <> -1)
83. BEGIN
84. IF (@@fetch_status <> -2)
85. BEGIN
86. PRINT ''
87. SET @tmpstr = '-- Login: ' + @name
88. PRINT @tmpstr
89. IF (@type IN ( 'G', 'U'))
90. BEGIN -- NT authenticated account/group
91.
92. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
93. END
94. ELSE BEGIN -- SQL Server authentication
95. -- obtain password and sid
96. SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
97. EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
98. EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
99.
100. -- obtain password policy state
101. 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
102. 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
103.
104. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
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. IF (@denylogin = 1)
116. BEGIN -- login is denied access
117. SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
118. END
119. ELSE IF (@hasaccess = 0)
120. BEGIN -- login exists but does not have access
121. SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
122. END
123. IF (@is_disabled = 1)
124. BEGIN -- login is disabled
125. SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
126. END
127. PRINT @tmpstr
128. END
129.
130. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
131. END
132. CLOSE login_curs
133. DEALLOCATE login_curs
134. RETURN 0
135. GO
Nota katak: Script ne’e kria kria store procedure rua iha master base de dadus. Procedure ne’e nia naran mak sp_hexadecimal ho sp_help_revlogin
3. Agora halao statement tuir mai ne’e:
EXEC sp_help_revlogin
Output script husi sp_help_revlogin sei kria procedur script ba login nian. Script login ne’e bainhira halao iha SQL server seluk nia kredencial hanesan lolos ho kredencial iha fatin ne’ebe ita exporta database, hanesan SID (security Identifier) no original password
4. Iha server B, loke SQL server Management Studio, no liga ba iha instancia base de dadus iha SQL Server
Note: Molok atu ba iha pasu 5. Hare’e didika lai nia prosedural
5. Loke Window Editor Query nian ida, no halao output ida ne’ebe ita hetan iha pasu 3.
Tested with successful result.
0 Comments