give full permission to all stored procedure

Question

How to set user to give full permission to all stored procedure in sql server?

i have 217 Stored procedure

Answer 1

Hi @Devendra Kumar Sahu ,

Please make sure you have enough permissions, it requires ALTER PROCEDURE permission on the procedure.
And please note that if the previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are enabled only if they are included in the ALTER PROCEDURE statement.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Devendra Kumar Sahu ,

Please check out this doc:
Grant Permissions on a Stored Procedure

This example grants EXECUTE permission to all stored procedures that exist, or will exist, in the HumanResources schema, to an application role named Recruiting11.

USE AdventureWorks2012;     
GRANT EXECUTE ON SCHEMA::HumanResources  
    TO Recruiting11;    
GO  

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Are all stored procedures in the same schema, like "dbo"? Then you can grant execute permissions on the schema as it, like

GRANT EXECUTE ON SCHEMA::dbo TO [UserName]  

See
https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver16