{"id":2102,"date":"2014-02-21T11:00:35","date_gmt":"2014-02-21T16:00:35","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2102"},"modified":"2014-11-13T13:15:14","modified_gmt":"2014-11-13T18:15:14","slug":"deny-control-side-effects","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/","title":{"rendered":"The Unexpected Side Effect of DENY CONTROL"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSay, you want to give a user access to a database, but you want to prevent them getting a higher level permission like CONTROL DATABASE<\/span>, even if they might be a member of a role that has that permission. In DENY vs. REVOKE - Part 2 - Hierarchies of Principals<\/a> we saw that a DENY<\/span> always trumps a GRANT<\/span> even if the GRANT<\/span> happened to a role and the DENY<\/span> to a role member. So the first inclination you might have, is to just DENY CONTROLL ON DATABASE<\/span> to that user of yours.\n<\/p>\n

DENY CONTROL Example<\/h3>\n

\nLet's try out if that DENY CONTROL DATABASE<\/span> works out. First we need a user and a table in an example database:\n<\/p>\n

\n[sql]\nUSE TestDatabase1;
\nGO
\nCREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE LOGIN TestLogin2 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE USER TestUser1 FOR LOGIN TestLogin1;
\nGO
\nCREATE TABLE dbo.tst(id INT);
\nINSERT INTO dbo.tst VALUES(42);
\n[\/sql]\n<\/div>\n

\nThis creates a user with an associated login and the table we need. However, it also creates a second login that we will use in a later example.\n<\/p>\n

\nThe next step is to GRANT SELECT<\/span> on that table to TestUser1<\/span> and also DENY CONTROL<\/span> on the database:\n<\/p>\n

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

\nThis is odd. We setup the correct permission and used EXECUTE AS<\/span> to execute the SELECT<\/span> statement as TestUser1<\/span>. However we are getting an error saying the TestLogin1<\/span> cannot access the database. Any login that has an associated user in a database can access that database, I thought, but yet it seems this is not correct here.\n<\/p>\n

\nLet's try something else. Any login has access to the master database by default. There are no permissions that need to be set up for this and no users that need to be created in master. Now, having access to master does not mean being able to do everything in that database, but any login will be able to run some simple queries like this one:\n<\/p>\n

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

\nNow, we did not create a user for TestLogin2<\/span> in the master<\/span> database, so we cannot DENY CONTROL<\/span> on the database. However, we also don't want TestUser2<\/span> to take over control of the entire server, so let's deal with that first by using DENY CONTROL SERVER<\/span>:\n<\/p>\n

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

\nEven though we did not DENY<\/span> anything in master, TestLogin2<\/span> is suddenly not able to connect to it anymore. What happened?\n<\/p>\n

Denying All Inclusive Permissions<\/h3>\n

\nIn DENY vs. REVOKE - Part 4 - Hierarchies of Privileges<\/a> I told you that a DENY<\/span> anywhere in a hierarchy of privileges also trumps any GRANT<\/span> in that same hierarchy.\n<\/p>\n

\nCONTROL DATABASE<\/span> is a permission that includes any other database permission. So if we DENY CONTROL<\/span> on a database we inclusively deny all other privileges on that database too, particularly the CONNECT<\/span> privilege. The same holds true for the CONTROL SERVER<\/span> privilege. As the highest privilege in SQL Server it includes every other privilege. So denying CONTROL SERVER<\/span> effectively denies everything, period.\n<\/p>\n

Denying Just CONTROL<\/h3>\n

\nSo, how can I achieve to deny just the actions that require CONTROL<\/span> and that are not covered within any other permission? Sadly, I am not aware of any way to do this. The only option you have is to pay careful attention to not grant any permission to the principal that you did not indeed intend to include. You can consider using techniques like auditing to help you with this, but you cannot enforce it.\n<\/p>\n

Summary<\/h3>\n

\nExecuting a DENY CONTROL<\/span> can have unexpected side effects. Because of the hierarchical way denies work in SQL Server and because the CONTROL<\/span> privilege is always on the highest level of the privilege hierarchy, denying CONTROLL<\/span> effectively denies every single permission on the securable. For databases or even the server itself, that includes in particular the CONNECT<\/span> privilege, making any access to that entire resource impossible.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Denying a powerful privilege like CONTROL can have unforeseen side effects. Read on to see how using DENY CONTROL can render an entire security principal unusable.<\/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,99],"tags":[103,50,38,58,15],"class_list":["post-2102","post","type-post","status-publish","format-standard","hentry","category-general","category-security","category-security-pitfalls","tag-deny","tag-permission","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nThe Unexpected Side Effect of DENY CONTROL - sqlity.net<\/title>\n<meta name=\"description\" content=\"Denying a powerful privilege like CONTROL can have unforeseen side effects. E.g. when used on a database or the server it might make the principal unusable.\" \/>\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\/2102\/deny-control-side-effects\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Unexpected Side Effect of DENY CONTROL - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Denying a powerful privilege like CONTROL can have unforeseen side effects. E.g. when used on a database or the server it might make the principal unusable.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/\" \/>\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-21T16:00:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:15:14+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_CONTROL_DATABASE_in_action.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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Unexpected Side Effect of DENY CONTROL\",\"datePublished\":\"2014-02-21T16:00:35+00:00\",\"dateModified\":\"2014-11-13T18:15:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/\"},\"wordCount\":713,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_CONTROL_DATABASE_in_action.jpg\",\"keywords\":[\"DENY\",\"Permission\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\",\"Security Pitfalls\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/\",\"name\":\"The Unexpected Side Effect of DENY CONTROL - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_CONTROL_DATABASE_in_action.jpg\",\"datePublished\":\"2014-02-21T16:00:35+00:00\",\"dateModified\":\"2014-11-13T18:15:14+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Denying a powerful privilege like CONTROL can have unforeseen side effects. E.g. when used on a database or the server it might make the principal unusable.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_CONTROL_DATABASE_in_action.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_CONTROL_DATABASE_in_action.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2102\\\/deny-control-side-effects\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Unexpected Side Effect of DENY CONTROL\"}]},{\"@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":"The Unexpected Side Effect of DENY CONTROL - sqlity.net","description":"Denying a powerful privilege like CONTROL can have unforeseen side effects. E.g. when used on a database or the server it might make the principal unusable.","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\/2102\/deny-control-side-effects\/","og_locale":"en_US","og_type":"article","og_title":"The Unexpected Side Effect of DENY CONTROL - sqlity.net","og_description":"Denying a powerful privilege like CONTROL can have unforeseen side effects. E.g. when used on a database or the server it might make the principal unusable.","og_url":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-21T16:00:35+00:00","article_modified_time":"2014-11-13T18:15:14+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_CONTROL_DATABASE_in_action.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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Unexpected Side Effect of DENY CONTROL","datePublished":"2014-02-21T16:00:35+00:00","dateModified":"2014-11-13T18:15:14+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/"},"wordCount":713,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_CONTROL_DATABASE_in_action.jpg","keywords":["DENY","Permission","security","security management","SQL Server"],"articleSection":["General","Security","Security Pitfalls"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/","url":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/","name":"The Unexpected Side Effect of DENY CONTROL - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_CONTROL_DATABASE_in_action.jpg","datePublished":"2014-02-21T16:00:35+00:00","dateModified":"2014-11-13T18:15:14+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Denying a powerful privilege like CONTROL can have unforeseen side effects. E.g. when used on a database or the server it might make the principal unusable.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_CONTROL_DATABASE_in_action.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_CONTROL_DATABASE_in_action.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2102\/deny-control-side-effects\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Unexpected Side Effect of DENY CONTROL"}]},{"@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-xU","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2102","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=2102"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2102\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}