Назад | Перейти на главную страницу

Как правильно предоставить сохраненные разрешения на выполнение процесса для пользователя SQL Server?

Я только что переместил базу данных SQL Server 2000 в SQL Server 2005 Express (который скоро будет обновлен) и в процессе избавляюсь от некоторых вредных привычек предыдущего владельца.

Во-первых, старое веб-приложение использовало учетную запись «sa» для доступа к БД. Теперь я создал новый логин и сопоставил его с пользователем в этой конкретной базе данных с ролями db_datawriter и db_datareader. Логин работает нормально ... но когда приложение пытается выполнить любую сохраненную процедуру, я получаю сообщение об ошибке, что процедура не найдена. Это проблема с разрешениями ... Подключение к серверу через Management Studio с использованием новых учетных данных показывает мне, что ни один из сохраненных процессов приложения даже не виден, поэтому ошибка имеет смысл.

Но ... здесь хранятся две-три сотни процессов. Как мне предоставить этому пользователю доступ для выполнения любых сохраненных процедур, не изменяя разрешения для каждой из них? А пока я пошел и добавил роль «db_owner» для этого пользователя. Но это кажется излишним ...?

Ага, это перебор. Самый простой способ сделать это - предоставить пользователю права на выполнение всех процедур в схеме (или еще лучше предоставить роли это право).

Сначала создайте новую роль. Назовите его YourAppRole (или как-нибудь иначе, имя на самом деле не имеет значения). Сделайте свою учетную запись пользователя членом роли. Предоставьте роли права на выполнение схеме dbo (или любой другой схеме, в которой находятся процедуры). Это можно сделать в пользовательском интерфейсе или с помощью кода.

GRANT EXEC ON SCHEMA::dbo TO YourAppRole

Или вы можете написать сценарий T / SQL, чтобы пройти через все процедуры и предоставить право. Технически это более безопасный вариант.

DECLARE @proc sysname
DECLARE @cmd varchar(8000)

DECLARE cur CURSOR FOR select '[' + schema_name(schema_id) + '].[' + name + ']' from sys.procedures
OPEN cur
FETCH next from cur into @proc
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @cmd = 'GRANT EXEC ON ' + @proc + ' TO YourAppRole'
     EXEC (@cmd)

     FETCH next from cur into @proc
END
CLOSE cur
DEALLOCATE cur

Если все они находятся в одной схеме, вы можете разрешить выполнение в ней.

Что-то вроде...

предоставить пользователю выполнить на схеме :: dbo

Затем избавьтесь от других добавленных вами ролей, протестируйте и посмотрите, нужны ли ему разрешения для базовых таблиц.