Como eu concedo uma função de banco de dados para executar permissões em um esquema? O que estou fazendo de errado?

Question

Estou usando a edição do SQL Server 2008 Express.

Eu criei um Login, Usuário, Função e Esquema. Eu mapeei o usuário para o login e atribui a função ao usuário.

O esquema contém várias tabelas e procedimentos armazenados.

Eu gostaria que a função tivesse permissões de execução em todo o esquema.

Eu tentei conceder permissão de execução através do estúdio de gerenciamento e digitando o comando em uma janela de consulta.

GRANT EXEC ON SCHEMA::schema_name TO role_name

Mas quando me conecto ao banco de dados usando o SQL management studio (como o login que criei), em primeiro lugar não consigo ver os procedimentos armazenados, mas o mais importante é que recebo um erro de permissão negada ao tentar executá-los.

O procedimento armazenado em questão não faz nada exceto selecionar dados de uma tabela dentro do mesmo esquema.

Eu tentei criar o procedimento armazenado com e sem a linha:

WITH EXECUTE AS OWNER

Isso não faz diferença.

Suspeito que cometi um erro ao criar meu esquema ou há algum problema de propriedade em algum lugar, mas estou realmente com dificuldades para fazer algo funcionar.

A única maneira que consegui executar com sucesso os procedimentos armazenados é concedendo permissões de controle à função, bem como a execução, mas não acredito que essa seja a maneira correta e segura de prosseguir.

Qualquer sugestão / comentário seria muito apreciada.

Obrigado.

    

Answer 1

Eu tive boa sorte com a seguinte técnica abaixo (usando um esquema chamado "myschema" para o exemplo). Parece que você tem a maioria das peças no lugar, mas a propriedade schema / role pode não estar definida corretamente.

-- Create the role that will own the schema
CREATE ROLE [myschema_owner] AUTHORIZATION [dbo];

-- Create the role that will have execute permissions on the schema
CREATE ROLE [myschema_execute] AUTHORIZATION [dbo];

-- Create the schema owned by our role
CREATE SCHEMA [myschema] AUTHORIZATION [myschema_owner];

-- Assign execute permissions to execute role
GRANT SELECT, EXECUTE ON SCHEMA::[myschema] TO [myschema_execute];

    

Answer 2

Não sei como limitar isso a um determinado esquema (embora pareça que sua sintaxe deve funcionar), mas se for aceitável ter direitos de execução em todo o banco de dados, o seguinte fará o truque e, em seguida, adicione seu login o papel como Ankur descreve.

grant execute to <ROLE_NAME>

Além disso, conceder VIEW DEFINITION a essa função permitirá que os logins na função vejam os procedimentos armazenados.

    

Answer 3

Open SQL Server Management Studio using SA login or Windows Authentication

Go to Securities >> Logins

Right click on user and properties

Go to User Mappings

Select Database name

and assign permission

done