{"id":229,"date":"2012-05-29T08:31:52","date_gmt":"2012-05-29T08:31:52","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/05\/29\/grant-revoke-permissions-on-stored-procedures-in-sql-server\/"},"modified":"2012-05-29T08:31:52","modified_gmt":"2012-05-29T08:31:52","slug":"grant-revoke-permissions-on-stored-procedures-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/05\/29\/grant-revoke-permissions-on-stored-procedures-in-sql-server\/","title":{"rendered":"Grant Revoke Permissions On Stored Procedures in SQL Server"},"content":{"rendered":"

SQL Server Provides the following permission on the stored procedures:<\/p>\n

– ALTER
\n– CONTROL
\n– EXECUTE
\n– REFERENCES
\n– TAKE OWNERSHIP
\n– VIEW DEFINITION<\/p>\n

To Grant revoke permissions Expand Object Explorer and right click on the required stored procedure and select properties as shown below:<\/p>\n

\"\"<\/a>
Expand Object Explorer<\/figcaption><\/figure>\n

In the properties popup select Permissions, here you can grant or revoke the respective permissions.<\/p>\n

\"\"<\/a>
Stored Procedure Permissions<\/figcaption><\/figure>\n

T SQL Script to grant permissions on stored procedure:<\/p>\n

[sql]
\nGRANT ALTER ON OBJECT::dbo.test TO username
\nGRANT CONTROL ON OBJECT::dbo.test TO username
\nGRANT EXECUTE ON OBJECT::dbo.test TO username
\nGRANT REFERENCES ON OBJECT::dbo.test TO username
\nGRANT TAKE OWNERSHIP ON OBJECT::dbo.test TO username
\nGRANT VIEW DEFINITION ON OBJECT::dbo.test TO username
\n[\/sql]
\nNote:<\/em> dbo.test is the procedure name.<\/p>\n

T SQL Script to revoke permissions on stored procedure:
\n[sql]
\nREVOKE ALTER ON OBJECT::dbo.test TO username
\nREVOKE CONTROL ON OBJECT::dbo.test TO username
\nREVOKE EXECUTE ON OBJECT::dbo.test TO username
\nREVOKE REFERENCES ON OBJECT::dbo.test TO username
\nREVOKE TAKE OWNERSHIP ON OBJECT::dbo.test TO username
\nREVOKE VIEW DEFINITION ON OBJECT::dbo.test TO username
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2041],"tags":[2042,2043,2044,2045,2046,2047],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/229"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=229"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/229\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}