[스크랩] DB 로그인 정보 이관

http://blog.happydong.kr/292

DB 이전 작업을 진행하다 보면, DB의 계정 정보도 같이 옮겨 줘야하는 번거로움이 있다. 만약, 계정이 많지 않다면 DB를 옮겨 주고, 이전한 서버에서 하나하나 계정을 만들어 주면 된다. 하지만, 계정이 여러개이고, 각 계정의 비밀번호를 모른다면.... 대략 난감할 것 이다. 이런 경우에는 아래와 같은 방법으로 문제를 해결 할 수 있다. 

 1. 기존 DB 서버에 SQL Management Studio를 이용해 접속을 한다. 

 기존 DB서버에 접속후 SQL Management Studio를 실행한다. 그리고 쿼리 편집장을 열도록 한다. 

 2. 아래 쿼리를 복사해서 실행을 한다. 

view plaincopy to clipboardprint?

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  

USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l on ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l on ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state 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 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 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO

 ** 위 쿼리를 실행하면 두개의 프로시져가 Master DB에 생성 되었을 것이다. (sp_hexadecimal , sp_help_revlogin)

 3. 만들어진 프로시져 "sp_help_revlogin"을 실행한다. 

view plaincopy to clipboardprint?

1. EXEC sp_help_revlogin  

EXEC sp_help_revlogin

 ** 프로시져를 실행하면 모든 계정 정보가 출력 될 것 이다. 출력 된 내용을 메모장에 저장해 둔다. 

 4. 이전하려는 DB서버 접속 후 SQL Management Studio를 실행한다. 

 출력된 목록 중 시스템 계정을 제외한 사용자 계정을 복사해, 이전하려는 DB 서버에 SQL Management Studio를 실행 후 내용을(사용자 계정 스크립트) 복사해서 실행한다.

 5. 이전하려는 서버에서 계정이 잘 등록 되었는지, 로그인을 시도해 본다. 

 참고 : https://support.microsoft.com/en-us/kb/918992?wa=wsignin1.0

출처: http://blog.happydong.kr/292 [최고보다,최선을...]