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:
In the properties popup select Permissions, here you can grant or revoke the respective 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]