{"id":1884,"date":"2014-01-25T11:00:18","date_gmt":"2014-01-25T16:00:18","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1884"},"modified":"2014-11-13T13:20:10","modified_gmt":"2014-11-13T18:20:10","slug":"grant-option-delegate-permission-management","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/","title":{"rendered":"WITH GRANT OPTION: How to Delegate your Permission Management"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nIn yesterday's post about the T-SQL GRANT<\/span> statement<\/a> we discussed how to manage permissions in T-SQL. Usually, if you grant a specific permission to a security principal in SQL Server, that principal will not be able to turn around and grant that same permission to someone else. That is normally a good thing, as it makes sure that permission management stays under your control. But sometimes it is nice to delegate\u2026\n<\/p>\n

WITH GRANT OPTION<\/h3>\n

\nIf you have enough trust in a security principal (or the human behind that principal) that you want to allow them to pass on the permission as they see fit, you can use the WITH GRANT OPTION<\/span> extension to the GRANT<\/span> statement. The syntax looks like this:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser1 WITH GRANT OPTION;
\n[\/sql]\n<\/div>\n

\nThis is almost the same statement that we saw yesterday. The only difference is that three words were added to the end: WITH GRANT OPTION<\/span>. Those three words enable permission delegation. Let's look at an example.\n<\/p>\n

WITH GRANT OPTION in Action<\/h3>\n

\nFirst let us confirm that a simple GRANT without WITH GRANT OPTION<\/span> does not allow for delegation:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser1;
\nGO
\nEXECUTE AS USER='TestUser1';
\nGO
\nSELECT * FROM dbo.tst AS T;
\nGO
\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser2;
\nGO
\nREVERT;
\n[\/sql]\n<\/div>\n

\nExecuting these statements leads to this result:\n<\/p>\n

\n\"A<\/a>\n<\/p>\n

\nAs you can see, the permission to SELECT<\/span> was granted to TestUser1<\/span>. However, the attempt by TestUser1<\/span> to delegate that permission to TestUser2<\/span> fails with an - a little misleading - dbo.tst<\/span> not found error.\n<\/p>\n

\nIf TestUser2<\/span> now tries to access the data in that table the statement fails:\n<\/p>\n

\n\"Therefore<\/a>\n<\/p>\n

\nNow let's look at how the WITH GRANT OPTION<\/span> changes this picture by running this statement block, starting out with the extended GRANT<\/span>:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser1 WITH GRANT OPTION;
\nGO
\nEXECUTE AS USER='TestUser1';
\nGO
\nSELECT * FROM dbo.tst AS T;
\nGO
\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser2;
\nGO
\nREVERT;
\n[\/sql]\n<\/div>\n

\nFirst thing to notice is that this does not throw an error anymore:\n<\/p>\n

\n\"WITH<\/a>\n<\/p>\n

\nThat should mean that TestUser2<\/span> can now access the table. Let's confirm:\n<\/p>\n

\n\"Therefore<\/a>\n<\/p>\n

\nThe delegation of the SELECT<\/span> permission by TestUser1<\/span> to TestUser2<\/span> was successful.\n<\/p>\n

Summary<\/h3>\n

\nSometimes it is required for a security principal to be able to delegate a specific permission to other security principals. The simple GRANT<\/span> statement does not allow for that. However, to enable delegation, the GRANT<\/span> statement extension WITH GRANT OPTION<\/span> can be used.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Granting a permission to a user normally does not enable that user to pass the permission on. However the WITH GRANT OPTION extension to the GRANT statement enables just that. Read on to get all the details.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,34],"tags":[88,50,38,58,15],"class_list":["post-1884","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-grant","tag-permission","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nWITH GRANT OPTION: How to Delegate your Permission Management - sqlity.net<\/title>\n<meta name=\"description\" content=\"How do you enable permission delegation? How do you allow a user or login to grant a specific permission to another user or login? Find out here...\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"WITH GRANT OPTION: How to Delegate your Permission Management - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"How do you enable permission delegation? How do you allow a user or login to grant a specific permission to another user or login? Find out here...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-25T16:00:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:20:10+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Simple_GRANT_does_not_allow_for_Delegation.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"WITH GRANT OPTION: How to Delegate your Permission Management\",\"datePublished\":\"2014-01-25T16:00:18+00:00\",\"dateModified\":\"2014-11-13T18:20:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/\"},\"wordCount\":474,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Simple_GRANT_does_not_allow_for_Delegation.jpg\",\"keywords\":[\"GRANT\",\"Permission\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/\",\"name\":\"WITH GRANT OPTION: How to Delegate your Permission Management - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Simple_GRANT_does_not_allow_for_Delegation.jpg\",\"datePublished\":\"2014-01-25T16:00:18+00:00\",\"dateModified\":\"2014-11-13T18:20:10+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"How do you enable permission delegation? How do you allow a user or login to grant a specific permission to another user or login? Find out here...\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Simple_GRANT_does_not_allow_for_Delegation.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Simple_GRANT_does_not_allow_for_Delegation.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1884\\\/grant-option-delegate-permission-management\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"WITH GRANT OPTION: How to Delegate your Permission Management\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"WITH GRANT OPTION: How to Delegate your Permission Management - sqlity.net","description":"How do you enable permission delegation? How do you allow a user or login to grant a specific permission to another user or login? Find out here...","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/","og_locale":"en_US","og_type":"article","og_title":"WITH GRANT OPTION: How to Delegate your Permission Management - sqlity.net","og_description":"How do you enable permission delegation? How do you allow a user or login to grant a specific permission to another user or login? Find out here...","og_url":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-25T16:00:18+00:00","article_modified_time":"2014-11-13T18:20:10+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Simple_GRANT_does_not_allow_for_Delegation.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"WITH GRANT OPTION: How to Delegate your Permission Management","datePublished":"2014-01-25T16:00:18+00:00","dateModified":"2014-11-13T18:20:10+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/"},"wordCount":474,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Simple_GRANT_does_not_allow_for_Delegation.jpg","keywords":["GRANT","Permission","security","security management","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/","url":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/","name":"WITH GRANT OPTION: How to Delegate your Permission Management - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Simple_GRANT_does_not_allow_for_Delegation.jpg","datePublished":"2014-01-25T16:00:18+00:00","dateModified":"2014-11-13T18:20:10+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"How do you enable permission delegation? How do you allow a user or login to grant a specific permission to another user or login? Find out here...","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Simple_GRANT_does_not_allow_for_Delegation.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Simple_GRANT_does_not_allow_for_Delegation.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1884\/grant-option-delegate-permission-management\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"WITH GRANT OPTION: How to Delegate your Permission Management"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-uo","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1884","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1884"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1884\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1884"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1884"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1884"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}