Grant Revoke Permissions On Stored Procedures in SQL Server

SQL Server Provides the following permission on the stored procedures:

– ALTER
– CONTROL
– EXECUTE
– REFERENCES
– TAKE OWNERSHIP
– VIEW DEFINITION

To Grant revoke permissions Expand Object Explorer and right click on the required stored procedure and select properties as shown below:

Expand Object Explorer

In the properties popup select Permissions, here you can grant or revoke the respective permissions.

Stored Procedure Permissions

T SQL Script to grant permissions on stored procedure:

[sql]
GRANT ALTER ON OBJECT::dbo.test TO username
GRANT CONTROL ON OBJECT::dbo.test TO username
GRANT EXECUTE ON OBJECT::dbo.test TO username
GRANT REFERENCES ON OBJECT::dbo.test TO username
GRANT TAKE OWNERSHIP ON OBJECT::dbo.test TO username
GRANT VIEW DEFINITION ON OBJECT::dbo.test TO username
[/sql]
Note: dbo.test is the procedure name.

T SQL Script to revoke permissions on stored procedure:
[sql]
REVOKE ALTER ON OBJECT::dbo.test TO username
REVOKE CONTROL ON OBJECT::dbo.test TO username
REVOKE EXECUTE ON OBJECT::dbo.test TO username
REVOKE REFERENCES ON OBJECT::dbo.test TO username
REVOKE TAKE OWNERSHIP ON OBJECT::dbo.test TO username
REVOKE VIEW DEFINITION ON OBJECT::dbo.test TO username
[/sql]

Leave a Reply

Your email address will not be published. Required fields are marked *