{"id":2161,"date":"2014-03-02T11:00:12","date_gmt":"2014-03-02T16:00:12","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2161"},"modified":"2014-11-13T13:13:48","modified_gmt":"2014-11-13T18:13:48","slug":"schema-vs-table-owner","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/","title":{"rendered":"Schema Owner vs. Object Owner – Who wins?"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nIn SQL Server, by default, a securable that is part of a schema is owned by the owner of the schema. However, you can change the securable to be owned by a different principal. We know that any permission granted on the schema also applies to all securables that are part of that schema, at least when they are owned by the same principal. With that in mind you might ask, if that rule is still true for different owners. Let us investigate\n<\/p>\n

Example of an object not owned by the schema owner<\/h3>\n

\nTo start out we need two principals:\n<\/p>\n

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

\nThe next step is to create a schema, owned by one principal, and a table within that schema, owned by the other principal:\n<\/p>\n

\n[sql]\nCREATE SCHEMA TestSchema1 AUTHORIZATION TestUser1;
\nGO
\nCREATE TABLE TestSchema1.tst(id INT);
\nALTER AUTHORIZATION ON OBJECT::TestSchema1.tst TO TestUser2;
\n[\/sql]\n<\/div>\n

\nRemember, while some securables allow the owner to be specified during creation as in the CREATE SCHEMA<\/span> statement above, others, like a table require a separate change-the-owner statement.\n<\/p>\n

\nTo see that the assignment of ownership worked as expected, we can use this query:\n<\/p>\n

\n[sql]\nSELECT S.name AS schema_name,USER_NAME(S.principal_id) AS owner_name
\n FROM sys.schemas AS S
\n WHERE S.name = 'TestSchema1';<\/p>\n

SELECT T.name AS table_name,
\n USER_NAME(T.principal_id) AS owner_name,
\n SCHEMA_NAME(T.schema_id) AS schema_name
\n FROM sys.tables AS T
\n WHERE T.name = 'tst';
\n[\/sql]\n<\/p><\/div>\n

\nIt produces the following output:\n<\/p>\n

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

\nKnow let us look at the effects of the ownership setup on the permissions. We know that TestUser2<\/span>, owning the table, should have unrestricted access to it. However, to be sure, let us confirm that this is indeed the case by running the following query:\n<\/p>\n

\n[sql]\nEXECUTE AS USER='TestUser2';
\nGO
\nSELECT * FROM sys.fn_my_permissions('TestSchema1.tst','OBJECT') AS FMP;
\nGO
\nREVERT;
\n[\/sql]\n<\/div>\n

\nIt produces this output:\n<\/p>\n

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

\nAs you can see, the CONTROL<\/span> privilege is part of the active permission set for TestUser2<\/span> which translates into unrestricted access.\n<\/p>\n

\nThe big question now is, if TestUser1<\/span> (as owner of TestSchema1<\/span>) still has unrestricted access to the table, even though the table itself is owned by TestUser2<\/span>. Let us try by running the above query again, but this time for TestUser1<\/span>:\n<\/p>\n

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

\nAs before, the CONTROL<\/span> permission is returned. That proves that the permission transfer along the hierarchy of securables is independent of the owner of those securables. Just to clarify, this is true for other principals that have been granted a privilege on the schema too, not only for the owner of the schema.\n<\/p>\n

\nSo, to answer the question in the title: Neither wins, both can happily coexist.\n<\/p>\n

Summary<\/h3>\n

\nA Schema and a table that is part of that schema have two different owners. However, a permission granted on the schema still automatically applies to each contained table (or any other securable) as well, independent of them having different owners. In particular, the owner of the schema has always unrestricted access to every securable that is part of that schema.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

A table might have a different owner than the schema it is contained in. Does a permission granted on the schema still apply to the table in that case? Read on to find out now.<\/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":[125,50,38,58,15],"class_list":["post-2161","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-owner","tag-permission","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nSchema Owner vs. Object Owner - Who wins? - sqlity.net<\/title>\n<meta name=\"description\" content=\"A table might have a different owner than its schema. Does a permission granted on the schema still apply to the table in that case? Find out now.\" \/>\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\/2161\/schema-vs-table-owner\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Schema Owner vs. Object Owner - Who wins? - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"A table might have a different owner than its schema. Does a permission granted on the schema still apply to the table in that case? Find out now.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/\" \/>\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-03-02T16:00:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:13:48+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/different_owners_for_schema_and_table.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\\\/2161\\\/schema-vs-table-owner\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Schema Owner vs. Object Owner – Who wins?\",\"datePublished\":\"2014-03-02T16:00:12+00:00\",\"dateModified\":\"2014-11-13T18:13:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/\"},\"wordCount\":572,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/different_owners_for_schema_and_table.jpg\",\"keywords\":[\"owner\",\"Permission\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/\",\"name\":\"Schema Owner vs. Object Owner - Who wins? - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/different_owners_for_schema_and_table.jpg\",\"datePublished\":\"2014-03-02T16:00:12+00:00\",\"dateModified\":\"2014-11-13T18:13:48+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"A table might have a different owner than its schema. Does a permission granted on the schema still apply to the table in that case? Find out now.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/different_owners_for_schema_and_table.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/different_owners_for_schema_and_table.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2161\\\/schema-vs-table-owner\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Schema Owner vs. Object Owner – Who wins?\"}]},{\"@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":"Schema Owner vs. Object Owner - Who wins? - sqlity.net","description":"A table might have a different owner than its schema. Does a permission granted on the schema still apply to the table in that case? Find out now.","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\/2161\/schema-vs-table-owner\/","og_locale":"en_US","og_type":"article","og_title":"Schema Owner vs. Object Owner - Who wins? - sqlity.net","og_description":"A table might have a different owner than its schema. Does a permission granted on the schema still apply to the table in that case? Find out now.","og_url":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-02T16:00:12+00:00","article_modified_time":"2014-11-13T18:13:48+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/different_owners_for_schema_and_table.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\/2161\/schema-vs-table-owner\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Schema Owner vs. Object Owner – Who wins?","datePublished":"2014-03-02T16:00:12+00:00","dateModified":"2014-11-13T18:13:48+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/"},"wordCount":572,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/different_owners_for_schema_and_table.jpg","keywords":["owner","Permission","security","security management","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/","url":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/","name":"Schema Owner vs. Object Owner - Who wins? - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/different_owners_for_schema_and_table.jpg","datePublished":"2014-03-02T16:00:12+00:00","dateModified":"2014-11-13T18:13:48+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"A table might have a different owner than its schema. Does a permission granted on the schema still apply to the table in that case? Find out now.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/different_owners_for_schema_and_table.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/different_owners_for_schema_and_table.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2161\/schema-vs-table-owner\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Schema Owner vs. Object Owner – Who wins?"}]},{"@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-yR","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2161","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=2161"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2161\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2161"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2161"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}