Qual é a maneira correta de conceder permissões de execução de proc armazenado para um usuário do SQL Server?

Question

Acabei de transferir um banco de dados do SQL Server 2000 para o SQL Server 2005 Express (que será atualizado em breve) e, no processo, estou limpando alguns maus hábitos do proprietário anterior.

Por um lado, o aplicativo da Web antigo usava a conta "sa" para acessar o banco de dados. Agora criei um novo login e mapeei isso para um usuário nesse banco de dados específico com as funções db_datawriter e db_datareader. Login funciona bem ... mas quando o aplicativo tenta executar qualquer proc armazenado, recebo um erro sobre o proc não ser encontrado. É um problema de permissões ... Conectar-se ao servidor através do Management Studio usando as novas credenciais me mostra que nenhum dos procs armazenados do aplicativo é visível, então o erro faz sentido.

Mas ... existem dois ou trezentos procs armazenados aqui. Como eu concedo a este usuário o acesso para executar qualquer um dos procs armazenados sem ter que modificar permissões em cada um? Por enquanto eu fui e adicionei o papel "db_owner" para esse usuário. Mas isso parece um exagero ...?

    

Answer 1

Sim, isso é exagero. A maneira mais fácil de fazer isso é conceder aos usuários direitos para executar todos os procedimentos no esquema (ou melhor ainda conceder um papel a esse direito).

Primeiro, crie uma nova função. Chame de YourAppRole (ou qualquer outra coisa, o nome realmente não importa). Torne sua conta de usuário um membro da função. Conceda à função direitos de execução para o esquema dbo (ou qualquer esquema em que os procedimentos estejam). Isso pode ser feito na interface do usuário ou via código.

GRANT EXEC ON SCHEMA::dbo TO YourAppRole

Ou você pode escrever um script T / SQL para seguir todos os procedimentos e conceder o direito. Tecnicamente, esta é uma opção mais segura.

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

    

Answer 2

Se todos eles estiverem no mesmo esquema, você poderá conceder execução nele.

Algo como ...

concede ao usuário executar no schema :: dbo

Em seguida, elimine as outras funções adicionadas, teste e veja se precisa de permissões nas tabelas base