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

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

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



--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=  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(@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, которая так же позволяет делать данные операции.

 

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

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