Скриптование всех пользователей, ролей БД и их прав : Немножко всего .. из жизни администратора ms sql server

Часто необходимо заскриптовать пользователей базы данных, а затем их снова накатить либо на новой базе данных , либо на старой, к примеру такое часто необходимо при восстанвление тестовой среды.
Ниже приводится скрипт скриптует пользователей и их права:

--1 создание таблицы для логинов

print 'Создание таблиц для хранения учетных записей и их прав'

IF NOT EXISTS (SELECT * FROM tempdb.sys.objects
              WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[logins]') AND type in (N'U'))
begin
      create table tempdb.dbo.logins
      ( a varchar(2048),
       l int)
end
else
begin
truncate table tempdb.dbo.Logins
end

IF NOT EXISTS (SELECT * FROM tempdb.sys.objects

              WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[loginsscripts]') AND type in (N'U'))
begin
      create table tempdb.dbo.loginsscripts
      ( a varchar(2048))
end
else
begin
truncate table tempdb.dbo.[loginsscripts]
end
print 'Конец Создание таблиц для хранения учетных записей и их прав'

--2 создание процедур скриптования

print 'Создание процедур для скриптования логинов '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERGRANT]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[USERGRANT]( @user VARCHAR(200) ) AS
SET NOCOUNT ON
declare @uid int, @objname sysname, @act int, @type int, @sact varchar(1024), @login sysname, @rolename sysname
create table #qq( a varchar(2048))
select @uid=u.uid, @login=l.loginname
from sysusers u inner join master.dbo.syslogins l on u.sid=l.sid
where u.name=@user
if (@uid is null) begin
      raiserror( ''User not found!'', 18, 1)
      goto lblerror
end

insert into #qq values (''EXEC sp_grantdbaccess @loginame= '' collate latin1_general_cs_as +QUOTENAME(@login)+'',@name_in_db='' collate latin1_general_cs_as +QUOTENAME(@user))
insert into #qq values (''GO'' collate latin1_general_cs_as )
declare __qq1 cursor local fast_forward
for
select name from dbo.sysusers
where uid in ( select groupuid from dbo.sysmembers where memberuid = @uid)

open __qq1
fetch next from __qq1
into @rolename
while( @@fetch_status = 0) begin
      insert into #qq values(''EXEC sp_addrolemember @rolename='' collate latin1_general_cs_as +QUOTENAME(@rolename)+'', @membername='' collate latin1_general_cs_as +QUOTENAME(@user))

fetch next from __qq1

into @rolename

end

close __qq1
deallocate __qq1

insert into #qq values(''GO'' collate latin1_general_cs_as)

declare __qq cursor local fast_forward
for
select ''[''+USER_NAME(o.uid)+''].[''+o.name+'']'', p.action, p.protecttype
from sysprotects p, sysobjects o
where p.uid=@uid and p.id=o.id

open __qq
fetch next from __qq
into @objname, @act, @type

while (@@fetch_status=0) begin

      set @sact= case @type
                             when 204 then ''GRANT '' collate latin1_general_cs_as
                             when 205 then ''GRANT '' collate latin1_general_cs_as
                             when 206 then ''DENY '' collate latin1_general_cs_as
                        end

if (OBJECTPROPERTY ( OBJECT_ID ( @objname ), ''IsTableFunction'' ) = 1)

      set @sact= case @act
                             when 193 then @sact+'' SELECT '' collate latin1_general_cs_as
                             when 224 then @sact+'' EXECUTE '' collate latin1_general_cs_as
                             when 26 then @sact+'' REFERENCES '' collate latin1_general_cs_as
                        end
else
      set @sact= case @act
                             when 193 then @sact+'' SELECT '' collate latin1_general_cs_as
                             when 195 then @sact+'' INSERT '' collate latin1_general_cs_as
                             when 197 then @sact+'' UPDATE '' collate latin1_general_cs_as
                             when 196 then @sact+'' DELETE '' collate latin1_general_cs_as
                             when 224 then @sact+'' EXECUTE '' collate latin1_general_cs_as
                             when 26 then @sact+'' REFERENCES '' collate latin1_general_cs_as
                        end
      set @sact = @sact+''ON '' collate latin1_general_cs_as+@objname+'' TO '' collate latin1_general_cs_as+QUOTENAME(@user)
      if (@type = 204)
            set @sact = @sact+'' WITH GRANT OPTION'' collate latin1_general_cs_as
      insert into #qq values(@sact)
      fetch next from __qq
      into @objname, @act, @type
end
close __qq
deallocate __qq

insert into #qq values(''GO'')

select * from #qq
lblerror:
drop table #qq
'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ROLEGRANT]') AND type in (N'P', N'PC'))

BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ROLEGRANT]( @rolename sysname ) AS
SET NOCOUNT ON
declare @uid int, @objname sysname, @act int, @type int, @sact varchar(1024), @isapp bit
select @uid=uid, @isapp=CAST(isapprole as bit)
from sysusers where name=@rolename
if (@uid is null) begin
      raiserror(''Role not found'', 18, 1)
      goto lblerror
end

create table #qq( a varchar(2048))
if (@isapp =0)
      insert into #qq values (''EXEC sp_addrole @rolename='' collate latin1_general_cs_as + QUOTENAME(@rolename))

else if (@isapp is not null)

insert into #qq values (''EXEC sp_addapprole @rolename='' collate latin1_general_cs_as + QUOTENAME(@rolename) +'', @password= {password}'' collate latin1_general_cs_as )

insert into #qq values (''GO'' collate latin1_general_cs_as)
insert into #qq values (''GO'' collate latin1_general_cs_as)
declare __qq cursor local fast_forward
for
select ''[''+USER_NAME(o.uid)+''].[''+o.name+'']'', p.action, p.protecttype
from sysprotects p, sysobjects o
where p.uid=@uid and p.id=o.id
open __qq

fetch next from __qq

into @objname, @act, @type
while (@@fetch_status=0) begin
      set @sact= case @type
                             when 204 then ''GRANT '' collate latin1_general_cs_as
                             when 205 then ''GRANT '' collate latin1_general_cs_as
                             when 206 then ''DENY '' collate latin1_general_cs_as
                        end

if (OBJECTPROPERTY ( OBJECT_ID ( @objname ), ''IsTableFunction'' ) = 1)

set @sact = @sact+''ON '' collate latin1_general_cs_as +@objname+'' TO ''collate latin1_general_cs_as +QUOTENAME(@rolename)

      if (@type = 204)
            set @sact = @sact+'' WITH GRANT OPTION'' collate latin1_general_cs_as
      insert into #qq values(@sact)

fetch next from __qq

into @objname, @act, @type
end
close __qq
deallocate __qq
insert into #qq values(''GO'')

select * from #qq
drop table #qq
lblerror:
'
END
GO

--3 получаем логины

SET NOCOUNT on;

insert into tempdb.dbo.logins
select name
, case when type in ('R', 'A')
then 1 else 0 end as issqlrole
from sys.database_principals
where is_fixed_role = 0
and name not in ('dbo', 'guest', 'public', 'INFORMATION_SCHEMA', 'sys')

-- создаем курсор и создаем скрипт создания логинов

declare @usr nvarchar(2048)
declare @sql nvarchar(2048)
declare @l int

declare cur cursor for
select * from tempdb.dbo.logins
open cur

FETCH NEXT FROM cur INTO @usr,@l

WHILE @@FETCH_STATUS = 0
BEGIN
if @l=0
begin
       set @sql='SET NOCOUNT On;exec USERGRANT ['+@usr+']'
       insert into tempdb.dbo.loginsscripts
       exec (@sql)
end
else
begin
       set @sql='SET NOCOUNT On;exec ROLEGRANT ['+@usr+']'
       insert into tempdb.dbo.loginsscripts
       exec (@sql)
end
FETCH NEXT FROM cur INTO @usr,@l
END
CLOSE cur
DEALLOCATE cur
SET NOCOUNT off;
go

--------------------------
--накатывание сохраненных пользователей и их прав.
--при необходимости нужно поменять базу данных, либо это сделать после
--определенных действий
declare @scr nvarchar(2048)
declare @sql nvarchar(2048)

declare cur cursor for

select * from tempdb.dbo.loginsscripts where a<>'Go'
open cur

FETCH NEXT FROM cur INTO @scr

WHILE @@FETCH_STATUS = 0
BEGIN
print @scr
--exec (@scr) --необходимо раскомментировать при накатывание
FETCH NEXT FROM cur INTO @scr
END
CLOSE cur
DEALLOCATE cur
SET NOCOUNT off;

-- просмотр наших скриптов

select * from tempdb.dbo.loginsscripts

-- Удаление объектов , делайте после того как скрипты не нужны,

drop table tempdb.dbo.loginsscripts

drop table tempdb.dbo.logins
drop PROCEDURE [dbo].[ROLEGRANT]
drop PROCEDURE [dbo].[USERGRANT]
print 'Конец выполнения скрипта

В основе данного скрипта взяты две процедуры USERGRANT,ROLEGRANT которые были выловлены из программы SQLExecMS, которая так же позволяет делать данные операции.

Немножко всего .. из жизни администратора ms sql server

среда, 13 марта 2013 г.

Скриптование всех пользователей, ролей БД и их прав

Комментариев нет :

Отправить комментарий

среда, 13 марта 2013 г.

Скриптование всех пользователей, ролей БД и их прав

Комментариев нет :

Отправить комментарий

среда, 13 марта 2013 г.