{"id":1893,"date":"2014-01-26T11:00:33","date_gmt":"2014-01-26T16:00:33","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1893"},"modified":"2014-11-13T13:20:05","modified_gmt":"2014-11-13T18:20:05","slug":"grant-using-grant-grant-option-roles","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/","title":{"rendered":"GRANT … AS: Using GRANT … WITH GRANT OPTION for Roles"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday we discussed how to enable users and logins to pass on their permissions<\/a>. That works great in most cases. However there is a small complication:\n<\/p>\n

Does GRANT ... WITH GRANT OPTION not work on roles?<\/h3>\n

\nWe know that server roles<\/a> and database roles<\/a> can significantly simplify the management of permissions. We also just discovered the ability to delegate permission management. Armed with that information let's try out to combine these two options. First we need to create two logins and two users; we also need a database role to put one of the users in:\n<\/p>\n

\n[sql]\nCREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE LOGIN TestLogin2 WITH PASSWORD='********', CHECK_POLICY = OFF;<\/p>\n

CREATE USER TestUser1 FOR LOGIN TestLogin1;
\nCREATE USER TestUser2 FOR LOGIN TestLogin2;<\/p>\n

CREATE ROLE TestRole1;
\nALTER ROLE TestRole1 ADD MEMBER TestUser1;
\n[\/sql]\n<\/p><\/div>\n

\nNow we can use the GRANT ... WITH GRANT OPTION<\/span> statement to give all members in TestRole1<\/span> the ability to pass on the permission:\n<\/p>\n

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

\nThat executes without problems:\n<\/p>\n

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

\nSo we should expect that TestUser1<\/span> will now be able to use the GRANT<\/span> statement to pass the permission on: <\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser2;
\n [\/sql]\n<\/div>\n

\nHowever, when we actually try it out, we get a different result:\n<\/p>\n

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

\nUnsurprisingly, the permission was also not granted:\n<\/p>\n

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

\nLet's review what we did. We know that any permission granted to a role automatically extends to all its members. We also know that GRANT ... WITH GRANT OPTION<\/span> allows the grantee to pass that permission on. In our case the grantee of the WITH GRANT OPTION<\/span> is the TestRole1<\/span> role, so we expect that the ability to pass on this permission applies to all role members. Finally, we know that TestUser1<\/span> is a member of that role. So, we did everything right. What went wrong?\n<\/p>\n

GRANT ... AS<\/h3>\n

\nIt turns out that the permission to delegation does not automatically transfer from a role to its members like other permission do. However, as a member of a role you can request it from that role, on a case by case basis. The syntax looks like this:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser2 AS TestRole1;
\n[\/sql]\n<\/div>\n

\nIt uses the additional AS<\/span> clause at the end to specify the role that is supplying the right to use the GRANT<\/span> statement. If the executing principal is a member of that role, the statement does not fail when executed:\n<\/p>\n

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

\nInstead it successfully passes the SELECT<\/span> permission on to TestUser2<\/span> as desired:\n<\/p>\n

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

Summary<\/h3>\n

\nThe ability to grant a specific permission does not automatically get passed down to members of a role. Instead it has to be explicitly requested by the role member executing the GRANT<\/span>. For that the GRANT...AS<\/span> statement can be used, specifying the role that has the delegation permission.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Permission delegation does not work with database and server roles as you would expect. Instead of an automatic permission cascade, every role member needs to take additional actions to delegate successfully. 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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[5,34],"tags":[59,57,88,50,38,58,56,55,15],"class_list":["post-1893","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-database-principals","tag-database-roles","tag-grant","tag-permission","tag-security-2","tag-security-management","tag-server-principals","tag-server-roles","tag-sql-server"],"acf":[],"yoast_head":"\nGRANT ... AS: Using GRANT ... WITH GRANT OPTION for Roles - sqlity.net<\/title>\n<meta name=\"description\" content=\"Delegate permissions do not cascade form roles to their members like ordinary permissions. The GRANT...AS statement allows requesting a cascade explicitly.\" \/>\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\/1893\/grant-using-grant-grant-option-roles\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"GRANT ... AS: Using GRANT ... WITH GRANT OPTION for Roles - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Delegate permissions do not cascade form roles to their members like ordinary permissions. The GRANT...AS statement allows requesting a cascade explicitly.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/\" \/>\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-26T16:00:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:20:05+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/using_WITH_GRANT_OPTION_on_a_database_role.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"GRANT … AS: Using GRANT … WITH GRANT OPTION for Roles\",\"datePublished\":\"2014-01-26T16:00:33+00:00\",\"dateModified\":\"2014-11-13T18:20:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/\"},\"wordCount\":513,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/using_WITH_GRANT_OPTION_on_a_database_role.jpg\",\"keywords\":[\"database principals\",\"database roles\",\"GRANT\",\"Permission\",\"security\",\"security management\",\"server principals\",\"server roles\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/\",\"name\":\"GRANT ... AS: Using GRANT ... WITH GRANT OPTION for Roles - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/using_WITH_GRANT_OPTION_on_a_database_role.jpg\",\"datePublished\":\"2014-01-26T16:00:33+00:00\",\"dateModified\":\"2014-11-13T18:20:05+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Delegate permissions do not cascade form roles to their members like ordinary permissions. The GRANT...AS statement allows requesting a cascade explicitly.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/using_WITH_GRANT_OPTION_on_a_database_role.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/using_WITH_GRANT_OPTION_on_a_database_role.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1893\\\/grant-using-grant-grant-option-roles\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"GRANT … AS: Using GRANT … WITH GRANT OPTION for Roles\"}]},{\"@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":"GRANT ... AS: Using GRANT ... WITH GRANT OPTION for Roles - sqlity.net","description":"Delegate permissions do not cascade form roles to their members like ordinary permissions. The GRANT...AS statement allows requesting a cascade explicitly.","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\/1893\/grant-using-grant-grant-option-roles\/","og_locale":"en_US","og_type":"article","og_title":"GRANT ... AS: Using GRANT ... WITH GRANT OPTION for Roles - sqlity.net","og_description":"Delegate permissions do not cascade form roles to their members like ordinary permissions. The GRANT...AS statement allows requesting a cascade explicitly.","og_url":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-26T16:00:33+00:00","article_modified_time":"2014-11-13T18:20:05+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/using_WITH_GRANT_OPTION_on_a_database_role.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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"GRANT … AS: Using GRANT … WITH GRANT OPTION for Roles","datePublished":"2014-01-26T16:00:33+00:00","dateModified":"2014-11-13T18:20:05+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/"},"wordCount":513,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/using_WITH_GRANT_OPTION_on_a_database_role.jpg","keywords":["database principals","database roles","GRANT","Permission","security","security management","server principals","server roles","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/","url":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/","name":"GRANT ... AS: Using GRANT ... WITH GRANT OPTION for Roles - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/using_WITH_GRANT_OPTION_on_a_database_role.jpg","datePublished":"2014-01-26T16:00:33+00:00","dateModified":"2014-11-13T18:20:05+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Delegate permissions do not cascade form roles to their members like ordinary permissions. The GRANT...AS statement allows requesting a cascade explicitly.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/using_WITH_GRANT_OPTION_on_a_database_role.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/using_WITH_GRANT_OPTION_on_a_database_role.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1893\/grant-using-grant-grant-option-roles\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"GRANT … AS: Using GRANT … WITH GRANT OPTION for Roles"}]},{"@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-ux","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1893","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=1893"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1893\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}