Ñíèìîê ÓÇ â SQL Server

Èñòî÷íèê: å-sql

Ïîÿâèëàñü íåîáõîäèìîñòü äåëàòü "ñíèìîê" âñåõ ëîãèíîâ íà ñåðâåðå ÁÄ ñî âñåìè ïðàâàìè âíóòðè ñåðâåðà è ïðàâàìè íà âñå áàçû, ñ âîçìîæíîñòüþ áûñòðî ñêðèïòîì âåðíóòü ýòî ñîñòîÿíèå áåç íàêàòûâàíèÿ ÁýêÀïîâ. Äëÿ ýòîãî ñëó÷àÿ ÿ íàâîÿë íåáîëüøîé ñêðèïò, äóìàþ îí áóäåò ìíîãèì ïîëåçåí, òàê êàê åù¸ îäíî åãî ïðèìåíåíèå-ýòî ïåðåíîñ ÓÇ ìåæäó ñåðâåðàìè ÁÄ. Çà îñíîâó âçÿë ñêðèïò îò microsoft (http://support.microsoft.com/kb/918992/ru), äîáàâèë ïîìèìî ïåðåíîñà ëîãèíîâ åù¸ è ïåðåíîñ ñåðâåðíûõ ðîëåé+ñîçäàíèå þçåðîâ ñî âñåìè ïðàâàìè íà áàçû.


Òåñòèðîâàëñÿ ñêðèïò íà SQL Server 2008/2008 R2(CTP):

001.USE master
002.GO
003.IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
004.  DROP PROCEDURE sp_hexadecimal
005.GO
006.CREATE PROCEDURE sp_hexadecimal
007.    @binvalue varbinary(256),
008.    @hexvalue varchar (514) OUTPUT
009.AS
010.DECLARE @charvalue varchar (514)
011.DECLARE @i int
012.DECLARE @length int
013.DECLARE @hexstring char(16)
014.SELECT @charvalue = '0x'
015.SELECT @i = 1
016.SELECT @length = DATALENGTH (@binvalue)
017.SELECT @hexstring = '0123456789ABCDEF'
018.WHILE (@i <= @length)
019.BEGIN
020.  DECLARE @tempint int
021.  DECLARE @firstint int
022.  DECLARE @secondint int
023.  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
024.  SELECT @firstint = FLOOR(@tempint/16)
025.  SELECT @secondint = @tempint - (@firstint*16)
026.  SELECT @charvalue = @charvalue +
027.    SUBSTRING(@hexstring, @firstint+1, 1) +
028.    SUBSTRING(@hexstring, @secondint+1, 1)
029.  SELECT @i = @i + 1
030.END
031. 
032.SELECT @hexvalue = @charvalue
033.GO
034. 
035.IF OBJECT_ID ('sp_help_revlogin_with_roles') IS NOT NULL
036.  DROP PROCEDURE sp_help_revlogin_with_roles
037.GO
038.CREATE PROCEDURE sp_help_revlogin_with_roles @login_name sysname = NULL AS
039.DECLARE @name sysname
040.DECLARE @type varchar (1)
041.DECLARE @hasaccess int
042.DECLARE @denylogin int
043.DECLARE @is_disabled int
044.DECLARE @PWD_varbinary  varbinary (256)
045.DECLARE @PWD_string  varchar (514)
046.DECLARE @SID_varbinary varbinary (85)
047.DECLARE @SID_string varchar (514)
048.DECLARE @tmpstr  varchar (1024)
049.DECLARE @is_policy_checked varchar (3)
050.DECLARE @is_expiration_checked varchar (3)
051. 
052.DECLARE @defaultdb sysname
053. 
054.DECLARE @srvrolemember sysname
055.DECLARE @str varchar(max)
056. 
057.IF (@login_name IS NULL)
058.  DECLARE login_curs CURSOR FOR
059. 
060.      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
061.sys.server_principals p LEFT JOIN sys.syslogins l
062.      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
063.ELSE
064.  DECLARE login_curs CURSOR FOR
065. 
066.      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
067.sys.server_principals p LEFT JOIN sys.syslogins l
068.      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
069.OPEN login_curs
070. 
071.FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
072.IF (@@fetch_status = -1)
073.BEGIN
074.  PRINT 'Èìåíà íå íàéäåíû.'
075.  CLOSE login_curs
076.  DEALLOCATE login_curs
077.  RETURN -1
078.END
079.SET @tmpstr = '/* sp_help_revlogin script '
080.PRINT @tmpstr
081.SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
082.PRINT @tmpstr
083.PRINT ''
084.WHILE (@@fetch_status <> -1)
085.BEGIN
086.  IF (@@fetch_status <> -2)
087.  BEGIN
088.    PRINT ''
089.    SET @tmpstr = '-- Login: ' + @name
090.    PRINT @tmpstr
091. 
092.    SET @tmpstr = 'IF EXISTS (SELECT * FROM sys.server_principals WHERE name= ' + QUOTENAME( @name , '''') + ' ) DROP LOGIN  ' + QUOTENAME( @name ) +';'
093.    PRINT @tmpstr
094. 
095.    IF (@type IN ( 'G', 'U'))
096.    BEGIN -- NT authenticated account/group
097. 
098.      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
099.    END
100.    ELSE BEGIN -- SQL Server authentication
101.        -- obtain password and sid
102.            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
103.        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
104.        EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
105. 
106.        -- obtain password policy state
107.        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
108.        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
109. 
110.            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
111. 
112.        IF ( @is_policy_checked IS NOT NULL )
113.        BEGIN
114.          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
115.        END
116.        IF ( @is_expiration_checked IS NOT NULL )
117.        BEGIN
118.          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
119.        END
120.    END
121.    IF (@denylogin = 1)
122.    BEGIN -- login is denied access
123.      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
124.    END
125.    ELSE IF (@hasaccess = 0)
126.    BEGIN -- login exists but does not have access
127.      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
128.    END
129.    IF (@is_disabled = 1)
130.    BEGIN -- login is disabled
131.      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE;'
132.    END
133.    PRINT @tmpstr
134.  END
135. 
136.        --sp_addsrvrolemember
137.        DECLARE srvrolemember_curs CURSOR FOR
138.                    SELECT r.name FROM sys.server_role_members rm
139.                    INNER JOIN
140.                    sys.server_principals r ON rm.role_principal_id=r.principal_id
141.                    INNER JOIN
142.                    sys.server_principals p ON rm.member_principal_id=p.principal_id
143.                    WHERE p.name=@name
144.        OPEN srvrolemember_curs
145.        FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember
146.        WHILE (@@fetch_status <> -1)
147.        BEGIN
148.        IF (@@fetch_status <> -2)
149.        BEGIN
150.        SET @tmpstr = 'EXEC sp_addsrvrolemember ' + QUOTENAME( @name ) + ', '+ QUOTENAME( @srvrolemember ) + ';'
151.        PRINT @tmpstr
152.        END
153.        FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember
154.        END
155.        CLOSE srvrolemember_curs
156.        DEALLOCATE srvrolemember_curs
157. 
158.        --CREATE USERS
159.        set @str='USE ?
160.        IF EXISTS (SELECT * FROM sys.database_principals WHERE sid='+
161.        CONVERT (VARCHAR(514), @SID_varbinary, 1)+')
162.        BEGIN
163.        DECLARE @name sysname, @schema sysname
164.        SELECT @name=name, @schema=default_schema_name FROM sys.database_principals WHERE sid='+
165.        CONVERT (VARCHAR(514), @SID_varbinary, 1)+
166.        'print "USE ?;
167.        IF EXISTS (SELECT * FROM sys.database_principals WHERE name=''"+@name+"'')
168.        DROP USER "+QUOTENAME(@name)+";
169.        CREATE USER "+QUOTENAME(@name)+" FOR LOGIN '+ QUOTENAME( @name ) +' WITH DEFAULT_SCHEMA = "+QUOTENAME(@schema)+";"
170. 
171.            DECLARE @dbrolemember sysname
172.            DECLARE dbrolemember_curs CURSOR FOR
173.            SELECT r.name FROM sys.database_role_members rm
174.            INNER JOIN
175.            sys.database_principals r ON rm.role_principal_id=r.principal_id
176.            INNER JOIN
177.            sys.database_principals p ON rm.member_principal_id=p.principal_id
178.            WHERE p.name=@name
179.            OPEN dbrolemember_curs
180.            FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember
181.            WHILE (@@fetch_status <> -1)
182.            BEGIN
183.            IF (@@fetch_status <> -2)
184.            BEGIN
185.            PRINT "EXEC sp_addrolemember "+QUOTENAME(@dbrolemember)+", "+QUOTENAME(@name)+";"
186.            END
187.            FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember
188.            END
189.            CLOSE dbrolemember_curs
190.            DEALLOCATE dbrolemember_curs
191.        END
192.        '
193.        EXECUTE sp_MSforeachdb @str
194. 
195.  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
196.   END
197.CLOSE login_curs
198.DEALLOCATE login_curs
199.RETURN 0
200.GO

Ïðèìå÷àíèå. Ñöåíàðèé ñîçäàåò â áàçå äàííûõ master äâå õðàíèìûõ ïðîöåäóðû - sp_hexadecimal è sp_help_revlogin_with_roles.

Äàëåå âûçûâàåì ïðîöåäóðó è ïîëó÷àåì ñêðèïò-ñíèìîê ñîñòîÿíèÿ ÓÇ íà ñåðâåðå ÁÄ:

1.EXEC master..sp_help_revlogin_with_roles

Ñöåíàðèé, êîòîðûé ñîçäàåòñÿ õðàíèìîé ïðîöåäóðîé sp_help_revlogin_with_roles, ÿâëÿåòñÿ ñöåíàðèåì âõîäà. Ýòîò ñöåíàðèé ñîçäàåò èìåíà âõîäà ñ èñõîäíûì èäåíòèôèêàòîðîì (ÈÄ) áåçîïàñíîñòè è ïàðîëåì.
À òàê æå íàçíà÷àåò ñåðâåðíûå ðîëè è ðîëè áàç äàííûõ.


Ñòðàíèöà ñàéòà http://test.interface.ru
Îðèãèíàë íàõîäèòñÿ ïî àäðåñó http://test.interface.ru/home.asp?artId=22672