Grant permission for the user to execute all stored procedures in the database?

Question

I created a script from an old database, created a new database, and imported all the data from the old database. However, so far it’s so good that the user does not have rights to execute stored procedures. I know I can use

GRANT EXECUTE ON [storedProcName] TO [userName] 

If I had only a few procedures, I had about 100, so what is the easiest way to give a user access to a specific user for all of them?

Thanks in advance.

Answer 1

Create a role, add this role to users, and then you can execute all the routines with one shot in this role.

 CREATE ROLE <abc> GRANT EXECUTE TO <abc> 

EDIT
This works in SQL Server 2005, I’m not sure about the backward compatibility of this function, I’m sure that something later than 2005 should be in order.

Answer 2

This is a solution, which means that as new stored procedures are added to the schema, users can execute them without having to call permission execution in the new stored procedure:

 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 eg 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 

Link: Granting permission to execute all stored procedures

Answer 3

use the code below, change the correct database name and username, and then execute this output and execute in SSMS. For SQL 2005 ABOVE

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

Answer 4

 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