{"id":2003,"date":"2014-02-09T11:00:25","date_gmt":"2014-02-09T16:00:25","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2003"},"modified":"2014-11-13T13:17:31","modified_gmt":"2014-11-13T18:17:31","slug":"deny-vs-revoke-2","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/","title":{"rendered":"DENY vs. REVOKE – Part 2 – Hierarchies of Principals"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday we asked this question: Are REVOKE<\/span> and DENY<\/span> just synonyms of each other?<\/a>\n<\/p>\n

\nWe did see that they behave identical when looking just at a single security principal and a single securable in isolation. However, I did promise to show you that there is indeed a difference between the two if you look at security hierarchies.\n<\/p>\n

\nTo follow up on that promise, today I am going to show you an example that involves roles.\n<\/p>\n

Role Member DENY Example<\/h3>\n

\nBefore we can get started we need a database role with a member and a table:\n<\/p>\n

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

CREATE ROLE TestRole1;
\nALTER ROLE TestRole1 ADD MEMBER TestUser1;<\/p>\n

CREATE TABLE dbo.tst(id INT);
\nINSERT INTO dbo.tst VALUES(42);
\n[\/sql]\n<\/p><\/div>\n

\nNow we can GRANT SELECT<\/span> on the table to TestRole1<\/span>. That will give TestUser1<\/span> access to the table, as TestUser1<\/span> is a member of TestRole1<\/span>:\n<\/p>\n

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

\nThis is how we expect role permissions to work. If you need a refresher on roles, check out my previous posts about database roles<\/a> and server roles<\/a>.\n<\/p>\n

\nNow let's REVOKE<\/span> the SELECT<\/span> permission on the table from TestUser1<\/span> and see what that does to TestUser1<\/span>'s ability to access to table:\n<\/p>\n

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

\nAs you can see, TestUser1<\/span> still has access to the table. When I introduced the REVOKE<\/span> statement<\/a> a few days ago I wrote: \"The REVOKE statement causes a previously granted identical privilege to be revoked. It works only on the same privilege on the same securable for the same grantee.\"\n<\/p>\n

\nWith those two sentences in mind, the above behavior is exactly as expected. The permission in question was granted to the role, so trying to revoke it from the role member should not do anything.\n<\/p>\n

\nNow let's see what happens if we use DENY<\/span> instead:\n<\/p>\n

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

\nNow TestUser1<\/span> does not have access to the table anymore. So, other than a REVOKE<\/span> that just can remove existing identical grants, a DENY<\/span> overrules grants on different levels of the principal hierarchy.\n<\/p>\n

\nIn my post about The Secret of the Security Token<\/a> I showed you that SQL Server actually looks at quite a few principals when determining the active permissions for the current request. The token DMVs return together one row for each principal involved. That includes in particular any database role that the current user is a member of. If any of those principals has been granted access to the securable, access is permitted. However, if any other database or server principal in that list has been denied access to that securable, the DENY<\/span> overrides the grant and access is refused.\n<\/p>\n

\nThis means in particular, that it does not matter which of the principals was granted and which was denied. Any deny anywhere in the hierarchy overrides any number of grants on the same resource.\n<\/p>\n

Summary<\/h3>\n

\nWhile REVOKE<\/span> and DENY<\/span> might seem similar at first glance, the DENY<\/span> statement is actually a lot more powerful as it can override a GRANT<\/span> that happened at a different level of the principal hierarchy.\n<\/p>\n

DENY vs. REVOKE Series<\/h3>\n

\nThis post is part of a five-part series comparing the DENY<\/span> and the REVOKE<\/span> statements.
\nBelow is a list of links to the posts that are already available.\n<\/p>\n

\n
\n

DENY vs. REVOKE – Part 1 – Are they just synonyms?<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 2 – Hierarchies of Principals<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 3 – Hierarchies of Securables<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 4 – Hierarchies of Privileges<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 5 – Durability<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"

REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies. 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":[104,5,34,27],"tags":[59,57,103,98,38,58,51,56,55,15],"class_list":["post-2003","post","type-post","status-publish","format-standard","hentry","category-deny-vs-revoke","category-general","category-security","category-series","tag-database-principals","tag-database-roles","tag-deny","tag-revoke","tag-security-2","tag-security-management","tag-security-tokens","tag-server-principals","tag-server-roles","tag-sql-server"],"acf":[],"yoast_head":"\nDENY vs. REVOKE - Part 2 - Hierarchies of Principals - sqlity.net<\/title>\n<meta name=\"description\" content=\"REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies.\" \/>\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\/2003\/deny-vs-revoke-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DENY vs. REVOKE - Part 2 - Hierarchies of Principals - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/\" \/>\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-02-09T16:00:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:17:31+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_permission_in_effect_for_role_member.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\\\/2003\\\/deny-vs-revoke-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"DENY vs. REVOKE – Part 2 – Hierarchies of Principals\",\"datePublished\":\"2014-02-09T16:00:25+00:00\",\"dateModified\":\"2014-11-13T18:17:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/\"},\"wordCount\":628,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_permission_in_effect_for_role_member.jpg\",\"keywords\":[\"database principals\",\"database roles\",\"DENY\",\"REVOKE\",\"security\",\"security management\",\"Security Tokens\",\"server principals\",\"server roles\",\"SQL Server\"],\"articleSection\":[\"DENY vs. REVOKE\",\"General\",\"Security\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/\",\"name\":\"DENY vs. REVOKE - Part 2 - Hierarchies of Principals - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_permission_in_effect_for_role_member.jpg\",\"datePublished\":\"2014-02-09T16:00:25+00:00\",\"dateModified\":\"2014-11-13T18:17:31+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_permission_in_effect_for_role_member.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Role_permission_in_effect_for_role_member.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2003\\\/deny-vs-revoke-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DENY vs. REVOKE – Part 2 – Hierarchies of Principals\"}]},{\"@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":"DENY vs. REVOKE - Part 2 - Hierarchies of Principals - sqlity.net","description":"REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies.","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\/2003\/deny-vs-revoke-2\/","og_locale":"en_US","og_type":"article","og_title":"DENY vs. REVOKE - Part 2 - Hierarchies of Principals - sqlity.net","og_description":"REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies.","og_url":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-09T16:00:25+00:00","article_modified_time":"2014-11-13T18:17:31+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_permission_in_effect_for_role_member.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\/2003\/deny-vs-revoke-2\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"DENY vs. REVOKE – Part 2 – Hierarchies of Principals","datePublished":"2014-02-09T16:00:25+00:00","dateModified":"2014-11-13T18:17:31+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/"},"wordCount":628,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_permission_in_effect_for_role_member.jpg","keywords":["database principals","database roles","DENY","REVOKE","security","security management","Security Tokens","server principals","server roles","SQL Server"],"articleSection":["DENY vs. REVOKE","General","Security","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/","url":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/","name":"DENY vs. REVOKE - Part 2 - Hierarchies of Principals - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_permission_in_effect_for_role_member.jpg","datePublished":"2014-02-09T16:00:25+00:00","dateModified":"2014-11-13T18:17:31+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"REVOKE and DENY are similar at first glance. However, DENY can be very powerful when used in the context of security principal hierarchies.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_permission_in_effect_for_role_member.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Role_permission_in_effect_for_role_member.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2003\/deny-vs-revoke-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"DENY vs. REVOKE – Part 2 – Hierarchies of Principals"}]},{"@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-wj","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2003","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=2003"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2003\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}