Главная > SQL Server > Снимок учетных записей в SQL Server

Снимок учетных записей в SQL Server

SQL%20Server%20generic%20brand%20Grid%20h Как заскриптовать все логины на SQL Server, их серверные роли + пользователей с правами в базе данных?

SQL Server MVP Алексей Князев попробовал решить эту задачу и его заявка на победу 🙂 тут — Снимок УЗ в SQL Server , в его блоге T-SQL.RU.

За основу взят скрипт от microsoft (http://support.microsoft.com/kb/918992/ru), плюс немного интеллектуального труда и вот цитата T-SQL кодом из его блога:
———————————————
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_with_roles’) IS NOT NULL

  DROP PROCEDURE sp_help_revlogin_with_roles

GO

CREATE PROCEDURE sp_help_revlogin_with_roles @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

 

DECLARE @srvrolemember sysname

DECLARE @str varchar(max)

 

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 ‘Имена не найдены.’

  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

 

    SET @tmpstr = ‘IF EXISTS (SELECT * FROM sys.server_principals WHERE name= ‘ + QUOTENAME( @name , »») + ‘ ) DROP LOGIN  ‘ + QUOTENAME( @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

 

            —sp_addsrvrolemember

            DECLARE srvrolemember_curs CURSOR FOR

                             SELECT r.name FROM sys.server_role_members rm

                             INNER JOIN

                             sys.server_principals r ON rm.role_principal_id=r.principal_id

                             INNER JOIN

                             sys.server_principals p ON rm.member_principal_id=p.principal_id

                             WHERE p.name=@name

            OPEN srvrolemember_curs

            FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember

            WHILE (@@fetch_status <> -1)

            BEGIN

            IF (@@fetch_status <> -2)

            BEGIN

            SET @tmpstr = ‘EXEC sp_addsrvrolemember ‘ + QUOTENAME( @name ) + ‘, ‘+ QUOTENAME( @srvrolemember ) + ‘;’

            PRINT @tmpstr

            END

            FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember

            END

            CLOSE srvrolemember_curs

            DEALLOCATE srvrolemember_curs

 

            —CREATE USERS

            set @str=’USE ?

            IF EXISTS (SELECT * FROM sys.database_principals WHERE sid=’+

            CONVERT (VARCHAR(514), @SID_varbinary, 1)+’)

            BEGIN

            DECLARE @name sysname, @schema sysname

            SELECT @name=name, @schema=default_schema_name FROM sys.database_principals WHERE sid=’+

            CONVERT (VARCHAR(514), @SID_varbinary, 1)+

            ‘print "USE ?;

            IF EXISTS (SELECT * FROM sys.database_principals WHERE name=»"+@name+"»)

            DROP USER "+QUOTENAME(@name)+";

            CREATE USER "+QUOTENAME(@name)+" FOR LOGIN ‘+ QUOTENAME( @name ) +’ WITH DEFAULT_SCHEMA = "+QUOTENAME(@schema)+";"

 

                  DECLARE @dbrolemember sysname

                  DECLARE dbrolemember_curs CURSOR FOR

                  SELECT r.name FROM sys.database_role_members rm

                  INNER JOIN

                  sys.database_principals r ON rm.role_principal_id=r.principal_id

                  INNER JOIN

                  sys.database_principals p ON rm.member_principal_id=p.principal_id

                  WHERE p.name=@name

                  OPEN dbrolemember_curs

                  FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember

                  WHILE (@@fetch_status <> -1)

                  BEGIN

                  IF (@@fetch_status <> -2)

                  BEGIN

                  PRINT "EXEC sp_addrolemember "+QUOTENAME(@dbrolemember)+", "+QUOTENAME(@name)+";"

                  END

                  FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember

                  END

                  CLOSE dbrolemember_curs

                  DEALLOCATE dbrolemember_curs

            END

            ‘

            EXECUTE sp_MSforeachdb @str

 

  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 две хранимых процедуры — sp_hexadecimal и sp_help_revlogin_with_roles.
Далее вызываем процедуру и получаем скрипт-снимок состояния УЗ на сервере БД:

———————————————
EXEC
master..sp_help_revlogin_with_roles
———————————————

 
Применимо:
SQL Server 2005 и выше

Реклама
Рубрики:SQL Server
  1. Комментариев нет.
  1. No trackbacks yet.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: