{"id":2145,"date":"2014-02-28T11:00:44","date_gmt":"2014-02-28T16:00:44","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2145"},"modified":"2014-11-13T13:14:04","modified_gmt":"2014-11-13T18:14:04","slug":"alter-authorization","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/","title":{"rendered":"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nIn SQL Server every securable has an owner. The owner is an additional security concept that is separate from the three security hierarchies (securable<\/a>,privilege<\/a> and principal<\/a>).
\nSimilar to the database owner, who has unrestricted access to the database, and everything within, the owner of any securable has unrestricted access to that securable. Let us look at an example.\n<\/p>\n

ALTER AUTHORIZATION Example<\/h3>\n

\nFirst, we need a security principal:\n<\/p>\n

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

\nNow let us create a table and confirm that TestUser1<\/span> does not have any permissions to it:\n<\/p>\n

\n[sql]\nCREATE TABLE dbo.tst(id INT);
\nGO
\nEXECUTE AS USER='TestUser1';
\n SELECT * FROM sys.fn_my_permissions('dbo.tst','OBJECT') AS FMP
\n ORDER BY FMP.permission_name;
\nREVERT;
\n[\/sql]\n<\/div>\n

\nThis query first creates a table and then uses sys.fn_my_permissions<\/span> together with EXECUTE AS<\/span> to check for current permissions of TestUser1<\/span> on the table. The result looks like this:\n<\/p>\n

\n\"Non-owners<\/a>\n<\/p>\n

\nAs expected, TestUser1<\/span> does not have any permissions granted on that new table. Now let us make TestUser1<\/span> the owner of our table. For that we can use the ALTER AUTHORIZATION<\/span> statement like this:\n<\/p>\n

\n[sql]\nALTER AUTHORIZATION ON OBJECT::dbo.tst TO TestUser1;
\n[\/sql]\n<\/div>\n

\nThe ALTER AUTHORIZATION<\/span> statement is built similar to the GRANT<\/span> statement. It takes a target securable in the same form as the GRANT<\/span> statement and also a target principal. The only difference is that there is no privilege involved here. You can use ALTER AUTHORIZATION<\/span> on almost any securable that you can use the GRANT<\/span> statement on. The securable is required in most cases to be prefixed by the securable class, OBJECT<\/span> in the above example. Keep in mind that the principal has to be a server principal if the securable is a server scope securable like a SERVER ROLE<\/span>. You can get the full list of securable classes that you can use ALTER AUTHORIZATION<\/span> on in its Books Online entry<\/a>.\n<\/p>\n

\nExecuting the above query and re-checking TestUser1<\/span>'s access will produce this result:\n<\/p>\n

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

\nAs you can see, TestUser1<\/span> now has the CONTROL<\/span> privilege on the table. The CONTROL<\/span> privilege always implies all other privileges, giving TestUser1<\/span> unrestricted access.\n<\/p>\n

Summary<\/h3>\n

\nThe ALTER AUTHORIZATION<\/span> statement can be used to set the owner of a securable. The owner automatically has unrestricted access to the securable, even if no explicit permissions have been granted.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

ALTER AUTHORIZATION can be used to set the owner of a securable. Securable ownership is independent of the three security hierarchies. Read on to get all the details on how securable ownership might impact your security management.<\/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":[125,50,58,15],"class_list":["post-2145","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-owner","tag-permission","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nALTER AUTHORIZATION \u2013 Setting the Owner of a Securable - sqlity.net<\/title>\n<meta name=\"description\" content=\"ALTER AUTHORIZATION can be used to set the owner of a securable. Read on to discover how securable ownership might impact your security management.\" \/>\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\/2145\/alter-authorization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"ALTER AUTHORIZATION can be used to set the owner of a securable. Read on to discover how securable ownership might impact your security management.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/\" \/>\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-28T16:00:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:14:04+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/non_owners_do_not_have_access.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\\\/2145\\\/alter-authorization\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable\",\"datePublished\":\"2014-02-28T16:00:44+00:00\",\"dateModified\":\"2014-11-13T18:14:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/\"},\"wordCount\":452,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/non_owners_do_not_have_access.jpg\",\"keywords\":[\"owner\",\"Permission\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/\",\"name\":\"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/non_owners_do_not_have_access.jpg\",\"datePublished\":\"2014-02-28T16:00:44+00:00\",\"dateModified\":\"2014-11-13T18:14:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"ALTER AUTHORIZATION can be used to set the owner of a securable. Read on to discover how securable ownership might impact your security management.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/non_owners_do_not_have_access.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/non_owners_do_not_have_access.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2145\\\/alter-authorization\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable\"}]},{\"@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":"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable - sqlity.net","description":"ALTER AUTHORIZATION can be used to set the owner of a securable. Read on to discover how securable ownership might impact your security management.","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\/2145\/alter-authorization\/","og_locale":"en_US","og_type":"article","og_title":"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable - sqlity.net","og_description":"ALTER AUTHORIZATION can be used to set the owner of a securable. Read on to discover how securable ownership might impact your security management.","og_url":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-28T16:00:44+00:00","article_modified_time":"2014-11-13T18:14:04+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/non_owners_do_not_have_access.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\/2145\/alter-authorization\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable","datePublished":"2014-02-28T16:00:44+00:00","dateModified":"2014-11-13T18:14:04+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/"},"wordCount":452,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/non_owners_do_not_have_access.jpg","keywords":["owner","Permission","security management","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/","url":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/","name":"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/non_owners_do_not_have_access.jpg","datePublished":"2014-02-28T16:00:44+00:00","dateModified":"2014-11-13T18:14:04+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"ALTER AUTHORIZATION can be used to set the owner of a securable. Read on to discover how securable ownership might impact your security management.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2145\/alter-authorization\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/non_owners_do_not_have_access.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/non_owners_do_not_have_access.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2145\/alter-authorization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"ALTER AUTHORIZATION \u2013 Setting the Owner of a Securable"}]},{"@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-yB","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2145","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=2145"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2145\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}