Установить разрешения для всех объектов для пользователей SQL


1

У меня есть процедура, которая проходит через все объекты в базе данных и назначает им соответствующие разрешения для этого объекта. Я хочу знать, есть ли лучший способ сделать это? Я использую базу данных модели для создания новых баз данных, поэтому я должен запускать ее каждый раз при создании новой базы данных. Вот идея о том, что она выглядит (примечание: есть кусок отсутствует с самого начала, что капли для всех пользователей и воссоздает необходимые пользователь, это необходимо, так как изменения в Сида):

CREATE PROCEDURE usp_SetPermissions 
AS 
BEGIN 

DECLARE @CurrentId INT 
DECLARE @ObjectName NVARCHAR(128) 
DECLARE @Message NVARCHAR(160) 
DECLARE @Error INT 
DECLARE @Sql NVARCHAR(256) 

CREATE TABLE #tmpDbObjects 
(
    ID INT IDENTITY(1,1), 
    ObjectName NVARCHAR(128), 
    Completed BIT 
) 

INSERT #tmpDbObjects(ObjectName, Completed) 
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1 

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects) 
BEGIN 
    -- Pick first uncompleted object 
    SELECT TOP 1 @CurrentId = ID, 
       @ObjectName = ObjectName 
    FROM #tmpDbObjects 

    -- Grant permissions to DB user 
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
    EXEC sp_sqlexec @Sql 

    -- Update object completion 
    DELETE #tmpDbObjects 
    WHERE [Id] = @CurrentId 

    -- Clear variables 
    SET @Sql = NULL 
    SET @CurrentId = NULL 
END 

INSERT #tmpDbObjects(ObjectName, Completed) 
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1 

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects) 
    BEGIN 
    -- Pick first uncompleted object 
    SELECT TOP 1 @CurrentId = ID, 
       @ObjectName = ObjectName 
    FROM #tmpDbObjects 

    -- Grant permissions to DB user 
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql 

    -- Update object completion 
    DELETE #tmpDbObjects 
    WHERE [Id] = @CurrentId 

    -- Clear variables 
    SET @Sql = NULL 
    SET @CurrentId = NULL 
END 

Это сценарии продолжается делать подобные вещи для всех видов, функций и т. д. в базе данных. Любые идеи для ускорения этой вещи или есть лучший способ сделать это?

6

Однолинейные давать разрешения на уровне схемы для роли

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole 

и второй линии, чтобы добавить пользователей в роль

EXEC sp_addrolemember 'SomeRole', 'whatever user' 

И сделать это в модели, так что все новые базы данных наследуют ,

Причины, вы должны установить разрешения раз только:

  • Схема представляет собой контейнер для объектов.
  • Новые объекты наследуют разрешения от схемы
  • Роль является контейнером для пользователей
  • Новые пользователи добавляются к роли и наследующих

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

Вы также можете CREATE LOGIN with a SID, так что на всех ваших серверах все одинаково, и вы тоже не становитесь сиротами.

Если бы ты задал правильный вопрос, мы могли бы спасти вам некоторые кодирования ...

Лично и, наконец, я бы сказал, что это плохая практика на «нужно иметь», а не «одеяло сделать что-нибудь "

  0

Так что-то вроде: ' IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [тип] = 'R' и [название] = 'ReadOnlyUsers') '' CREATE РОЛИ [ReadOnlyUsers] '' EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser'' 'GRANT? (выберите? или?) ON SCHEMA :: dbo TO [ReadOnlyUsers]' 'GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' Как отличить EXEC, UPDATE, DELETE и т. Д., Если все объекты находятся под dbo? Например, ReadOnlyUser все равно должен будет хранить хранимые процедуры EXEC. 20 сен. 112011-09-20 15:27:51

  0

@David George: разрешения схемы применяются автоматически независимо от объекта: поэтому proc получает EXEC, а таблицы получают S/I/U/D. Ваш GRANT будет GRANT EXEX, SELECT, UPDATE и т. Д. ON ... 20 сен. 112011-09-20 16:33:55

  0

, поэтому я буду запускать 'GRANT EXEC, SELECT, UPDATE, INSERT, DELETE on SCHEMA :: dbo TO [ReadWriteUsers]' Разрешил бы это разрешение пользователю УДАЛИТЬ процедуру? 20 сен. 112011-09-20 18:54:03

  0

Нет, им нужен ALTER TABLE или такой для этого 20 сен. 112011-09-20 22:29:39