Grant Revoke Permissions On Stored Procedures in SQL Server

Posted on : 29-05-2012 | By : Devi Prasad | In : SQL Server Permissions

0



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:

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

Note: dbo.test is the procedure name.

T SQL Script to revoke permissions on stored procedure:

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 



Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.



Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.




Your Ad Here


Tags: , , , , ,