Accorder à un utilisateur le droit d'exécuter toutes les procédures stockées dans la base de données ?

Question

J'ai généré script à partir de l'ancienne base de données, créé une nouvelle base de données et importé toutes les données de l'ancienne base de données. Jusqu'ici tout va bien, cependant, aucun utilisateur n'a les droits d'exécution pour les procédures stockées. Je sais que je peux utiliser

GRANT EXECUTE ON [storedProcName] TO [userName] 

S'il ne s'agissait que de quelques procédures, j'en ai une centaine, alors quel est le moyen le plus simple pour moi d'accorder l'accès d'exécution à un utilisateur spécifique pour toutes ces procédures ?

Merci d'avance.

Answer 1

Créez un rôle, ajoutez ce rôle aux utilisateurs, et ensuite vous pouvez accorder l'exécution à toutes les routines en une seule fois à ce rôle.

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

EDITAR
Cela fonctionne dans SQL Server 2005, je ne suis pas sûr de la compatibilité ascendante de cette fonctionnalité, je suis sûr que tout ce qui est postérieur à 2005 devrait convenir.

Answer 2

Cette solution signifie que lorsque vous ajoutez de nouvelles procédures stockées au schéma, les utilisateurs peuvent les exécuter sans avoir à appeler grant execute sur la nouvelle procédure stockée :

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'asp_net')
DROP USER asp_net
GO

IF  EXISTS (SELECT * FROM sys.database_principals 
WHERE name = N'db_execproc' AND type = 'R')
DROP ROLE [db_execproc]
GO

--Create a database role....
CREATE ROLE [db_execproc] AUTHORIZATION [dbo]
GO

--...with EXECUTE permission at the schema level...
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

--http://www.patrickkeisler.com/2012/10/grant-execute-permission-on-all-stored.html
--Any stored procedures that are created in the dbo schema can be 
--executed by users who are members of the db_execproc database role

--...add a user e.g. for the NETWORK SERVICE login that asp.net uses
CREATE USER asp_net 
FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] 
WITH DEFAULT_SCHEMA=[dbo]
GO

--...and add them to the roles you need
EXEC sp_addrolemember N'db_execproc', 'asp_net';
EXEC sp_addrolemember N'db_datareader', 'asp_net';
EXEC sp_addrolemember N'db_datawriter', 'asp_net';
GO

Answer 3

Vous pouvez utiliser la suggestion ci-dessus... ou interroger votre schéma d'information :

declare @script varchar(max) = ''

select @script += 'grant execute on [' + r.ROUTINE_NAME + '] to <user or role>' + char(13) + char(10)
from INFORMATION_SCHEMA.ROUTINES as r

exec(@script)

Cela peut vous être utile si votre convention d'appellation des procédures stockées se prête à un regroupement logique... c'est-à-dire que vous pourriez ajouter une option where r.ROUTINE_NAME like 'get%' de sorte que vous n'accordiez que la permission d'obtenir des procédures.

Answer 4

Utilisez le code ci-dessous, changez le nom de la base de données et le nom de l'utilisateur, puis prenez cette sortie et exécutez-la dans SSMS. POUR SQL 2005 CI-DESSUS

USE <database_name> 
select 'GRANT EXECUTE ON ['+name+'] TO [userName]  '  from sys.objects  where type ='P' and is_ms_shipped = 0

Answer 5

USE [DATABASE]

DECLARE @USERNAME VARCHAR(500)

DECLARE @STRSQL NVARCHAR(MAX)

SET @USERNAME='[USERNAME] '
SET @STRSQL=''

select @STRSQL+=CHAR(13)+'GRANT EXECUTE ON ['+ s.name+'].['+obj.name+'] TO'+@USERNAME+';'
from
    sys.all_objects as obj
inner join
    sys.schemas s ON obj.schema_id = s.schema_id
where obj.type in ('P','V','FK')
AND s.NAME NOT IN ('SYS','INFORMATION_SCHEMA')

EXEC SP_EXECUTESQL @STRSQL