Блог

Я пытаюсь создать скрипт ПРЕДОСТАВЛЕНИЯ для базы данных.

База данных не может использовать какие-либо встроенные роли, поэтому мне нужно заново создать db_reader, db_writer и EXEC для хранимых процедур в скрипте ПРЕДОСТАВЛЕНИЯ, назначенном этой учетной записи службы.

Я пытаюсь автоматизировать это, вместо того, чтобы просматривать каждый элемент в базе данных и вручную создавать это.

Пока у меня есть это:

 /* USER_TABLE */
select   'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.'   name   ' TO [DOMAINuser]' from sys.objects where  type  =  'U' order by  name;

/* INTERNAL_TABLE */
select   'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.'   name   ' TO [DOMAINuser]' from sys.objects where  type  =  'IT' order by  name;

/* VIEW */
select   'GRANT SELECT ON dbo.'   name   ' TO [DOMAINuser]' from sys.objects where  type  =  'V' order by  name;

/* SQL_STORED_PROCEDURE */
select   'GRANT EXECUTE ON dbo.'   name   ' TO [DOMAINuser]' from sys.objects where  type  =  'P' order by  name;

/* SQL_TABLE_VALUED_FUNCTION */
select   'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.'   name   ' TO [DOMAINuser]' from sys.objects where  type  =  'TF' order by  name;

/* SQL_SCALAR_FUNCTION */
select   'GRANT EXECUTE ON dbo.'   name   ' TO [DOMAINuser]' from sys.objects where  type  =  'FN' order by  name;
  

Но я не уверен, какие права нужны всем остальным элементам, то есть: SERVICE_QUEUE, SQL_TRIGGER и т.д. (смотрите ниже) Кроме того, если приведенное выше верно.

 select  DISTINCT(type_desc), type as a from sys.objects WHERE type <> 'S';
  

— те, которые, я думаю, мне не нужны

• DEFAULT_CONSTRAINT (D)
• FOREIGN_KEY_CONSTRAINT (F)
• PRIMARY_KEY_CONSTRAINT (PK)
• SERVICE_QUEUE (SQ)
• UNIQUE_CONSTRAINT (UQ)
• SQL_TRIGGER (TR)

— Те, которые, как я считаю, мне нужны

• ПОЛЬЗОВАТЕЛЬСКАЯ ТАБЛИЦА (U)
• INTERNAL_TABLE (IT)
• ПРОСМОТР (V)
• SQL_STORED_PROCEDURE (P)
• SQL_TABLE_VALUED_FUNCTION (TF)
• SQL_SCALAR_FUNCTION (FN)

Заранее спасибо!

На мой взгляд, вам нужно сделать следующее:

 create role [DatabaseUser]
go
grant select to [DatabaseUser]
grant insert to [DatabaseUser]
grant update to [DatabaseUser]
grant delete to [DatabaseUser]
grant execute to [DatabaseUser]
go
  

Затем для каждого пользователя, которому вы хотите предоставить права, просто выполните

 exec sp_addrolemember 'DatabaseUser', 'DOMAINuser'
  

Вам следует избегать добавления явных разрешений для объектов и пользователей. Вы облегчите себе жизнь, если будете использовать роли и схемы базы данных для обеспечения необходимой безопасности. Вы можете заглянуть в мой блог, там больше об этой теме.

С уважением

Piotr

Предполагая, что все ваши объекты находятся в схеме dbo, быстрый и грязный способ заключается в следующем

 grant select on schema::dbo to [MyUser]
grant insert on schema::dbo to [MyUser]
grant update on schema::dbo to [MyUser]
grant delete on schema::dbo to [MyUser]
grant execute on schema::dbo to [MyUser]
  

Однако лучше всего использовать роли

 CREATE ROLE MyRole
GO
EXEC sp_addrolemember 'MyRole', 'MyUser'
GO
grant select on schema::dbo to [MyRole]
grant insert on schema::dbo to [MyRole]
grant update on schema::dbo to [MyRole]
grant delete on schema::dbo to [MyRole]
grant execute on schema::dbo to [MyRole]
GO
  

 declare @UserInformation table
(
    LocalId int identity(1,1) not null primary key,
    GrantToUser nvarchar(20) default null
);

DECLARE @SQL nvarchar(4000);
DECLARE @Owner sysname;
DECLARE @StoredProcedure sysname;

DECLARE @GrantToUser varchar(20);

declare @rowCount int;
declare @whereAt int;
declare @howMany int;

declare @object nvarchar(128);
DECLARE @RETURN int;

set nocount on;

DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE type in ('P','Fn') order by [name]

DECLARE mycursor scroll cursor
FOR
   select name from sysobjects
   where type = 'u'
   order by name;

DECLARE cursorViews scroll cursor
FOR
SELECT name AS view_name
FROM sys.views
order by name;

set nocount on;

set @GrantToUser = 'UserName1';
insert into @UserInformation(GrantToUser) values (@GrantToUser);
set @GrantToUser = 'UserName2';
insert into @UserInformation(GrantToUser) values (@GrantToUser);
set @GrantToUser = 'UserName2';
insert into @UserInformation(GrantToUser) values (@GrantToUser);

set @rowCount = (select isnull(count(LocalId),0) from @UserInformation);

if (@rowCount > 0)
begin

    set @whereAt = 1;

    while (@whereAt <= @rowCount)
    begin

        select
            @GrantToUser = GrantToUser
        from
            @UserInformation
        where
            LocalId = @whereAt;

        set @SQL = 'if exists(select * from dbo.sysusers where name = '''   @GrantToUser   ''' and uid < 16382)';
        print @SQL;
        set @SQL = 'begin';
        print @SQL;

        OPEN cursStoredProcedures

        -- "Prime the pump" and get the first row
        FETCH NEXT FROM cursStoredProcedures
        INTO @Owner, @StoredProcedure

        -- Cycle through the rows of the cursor
        -- And grant permissions
        WHILE (@@FETCH_STATUS = 0)
        BEGIN

        -- Create the SQL Statement. Since we’re giving
        -- access to all stored procedures, we have to
        -- use a two-part naming convention to get the owner.
        SET @SQL = '    GRANT EXECUTE ON ['   @Owner
          '].['   @StoredProcedure
          '] TO ['   @GrantToUser   '];'

        print @SQL;

        -- Get the next row
        FETCH NEXT FROM cursStoredProcedures
        INTO @Owner, @StoredProcedure
        END

        -- Clean-up after the cursor
        CLOSE cursStoredProcedures

       open mycursor
        fetch first from mycursor into @object

        while @@fetch_status <> -1
        begin
          if @@fetch_status <> -2
          begin

             set @SQL = '    grant SELECT, INSERT, UPDATE, DELETE on [dbo].[' @object '] to ['   @GrantToUser   '];';

             print @SQL;

          end
          fetch next from mycursor into @object
        end

        close mycursor

        open cursorViews
        fetch first from cursorViews into @object

        while @@fetch_status <> -1
        begin
          if @@fetch_status <> -2
          begin

             set @SQL = '    grant SELECT on [dbo].[' @object '] to ['   @GrantToUser   '];';

             print @SQL;

          end
          fetch next from cursorViews into @object
        end

        close cursorViews

        set @SQL = 'end;'
        print @SQL;

        set @whereAt = @whereAt   1;

    end

end

print 'go';

set nocount off;

deallocate cursorViews
DEALLOCATE cursStoredProcedures
deallocate mycursor

go