Знімок УЗ в SQL Server, Інші СУБД, Бази даних, статті

З’явилася необхідність робити “знімок” всіх логінів на сервері БД з усіма правами всередині сервера і правами на всі бази, з можливістю швидко скриптом повернути цей стан без накочування бекапа. Для цього випадку я навоял невеликий скрипт, думаю він буде багатьом корисний, тому що ще одне його застосування-це перенесення УЗ між серверами БД. За основу взяв скрипт від 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, Є сценарієм входу. Цей сценарій створює імена входу з вихідним ідентифікатором (ВД) безпеки та паролем.
А так же призначає серверні ролі і ролі баз даних.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*