{"id":2152,"date":"2014-03-01T11:00:30","date_gmt":"2014-03-01T16:00:30","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2152"},"modified":"2014-11-13T13:13:55","modified_gmt":"2014-11-13T18:13:55","slug":"the-mysterious-schema-owner","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/","title":{"rendered":"The Mysterious Schema Owner"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday we looked at the concept of securable owners<\/a>. Every securable in SQL Server has a single owner, for example a user or a database role. The owner automatically has unrestricted access to the securable, even without any implicit grants.\n<\/p>\n

\nToday I would like to invite you to look in a little more detail at the ownership concept for securables that belong to a schema.\n<\/p>\n

Schema Owner Example<\/h3>\n

\nAs always, to look at an example we first need a few principals in place:\n<\/p>\n

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

\nWe also need a schema:\n<\/p>\n

\n[sql]\nCREATE SCHEMA TestSchema1 AUTHORIZATION TestUser1;
\n[\/sql]\n<\/div>\n

\nMany securables allow us to specify an owner right in the create statement by using the AUTHORIZATION<\/span> clause as in the example above.\n<\/p>\n

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

\nHowever, there are also quite a few securable classes that do not have that option. For those securables, you have to use a separate ALTER AUTHORIZATION<\/span> statement to change the owner after the fact.\n<\/p>\n

\nOne example of a securable that does not allow to specify the owner in the create statement is the table. Let us look at what owner is selected when we create one.\n<\/p>\n

\n[sql]\nCREATE TABLE TestSchema1.tst(id INT);
\nGO
\nSELECT T.name,
\n USER_NAME(T.principal_id) AS owner_name,
\n SCHEMA_NAME(T.schema_id) AS schema_name
\nFROM sys.tables AS T
\nWHERE T.name = 'tst';
\n[\/sql]\n<\/div>\n

\nThe select statement returns the table name, the name of the owner and the name of the schema of our newly created table. It produces this output:\n<\/p>\n

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

\nBut hold on, it seems that there is no owner set at all. This should not be possible. Let us quickly re-confirm that tables actually can have an owner:\n<\/p>\n

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

\nOK, so when we explicitly change the owner of a table it shows up in sys.tables<\/span>. But after creation there is no owner set?\n<\/p>\n

\nWell, there actually is an owner set. In the case of securables that live inside a schema like database objects or types a special rule applies. Upon creation, the securable is automatically owned by the owner of the schema. That is indicated by the fact that there is no explicit owner set after creation of such a securable and the respective catalog view returns principal_id = NULL<\/span>.\n<\/p>\n

\nTo default the schema owner as owner of all contained objects makes a lot of sense from a security management perspective. It for example makes for more predictable ownership chaining, a concept I will cover in a later post.\n<\/p>\n

\nIf you have changed the owner of an object and would like to set it back to be owned by the schema owner, you can use the ALTER AUTHORIZATION<\/span> clause like this:\n<\/p>\n

\n[sql]\nALTER AUTHORIZATION ON OBJECT::TestSchema1.tst TO SCHEMA OWNER;
\n[\/sql]\n<\/div>\n

\nIt sets the principal_id<\/span> back to NULL<\/span>, indicating that the table is now owned by the schema owner again:\n<\/p>\n

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

\nThe same syntax works with other securables that live in a schema too.\n<\/p>\n

Summary<\/h3>\n

\nSecurables that live inside a schema, like tables or types, are by default owned by the owner of the schema itself. This is indicated by a NULL<\/span> in the principal_id<\/span> column of the respective catalog view.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Have you ever noticed a principal_id of NULL in one of the catalog views, indicating that no owner is set for that securable? Discover how this might impact your security management and what the schema owner has to do with it.<\/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-2152","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":"\nThe Mysterious Schema Owner - sqlity.net<\/title>\n<meta name=\"description\" content=\"Have you ever noticed a principal_id of NULL in one of the catalog views? Discover how this might impact you and what the schema owner has to do with it.\" \/>\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\/2152\/the-mysterious-schema-owner\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Mysterious Schema Owner - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Have you ever noticed a principal_id of NULL in one of the catalog views? Discover how this might impact you and what the schema owner has to do with it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-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-01T16:00:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:13:55+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/schema_with_specified_AUTHORIZATION.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\\\/2152\\\/the-mysterious-schema-owner\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Mysterious Schema Owner\",\"datePublished\":\"2014-03-01T16:00:30+00:00\",\"dateModified\":\"2014-11-13T18:13:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/\"},\"wordCount\":566,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/schema_with_specified_AUTHORIZATION.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\\\/2152\\\/the-mysterious-schema-owner\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/\",\"name\":\"The Mysterious Schema Owner - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/schema_with_specified_AUTHORIZATION.jpg\",\"datePublished\":\"2014-03-01T16:00:30+00:00\",\"dateModified\":\"2014-11-13T18:13:55+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Have you ever noticed a principal_id of NULL in one of the catalog views? Discover how this might impact you and what the schema owner has to do with it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/schema_with_specified_AUTHORIZATION.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/schema_with_specified_AUTHORIZATION.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2152\\\/the-mysterious-schema-owner\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Mysterious Schema Owner\"}]},{\"@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 Mysterious Schema Owner - sqlity.net","description":"Have you ever noticed a principal_id of NULL in one of the catalog views? Discover how this might impact you and what the schema owner has to do with it.","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\/2152\/the-mysterious-schema-owner\/","og_locale":"en_US","og_type":"article","og_title":"The Mysterious Schema Owner - sqlity.net","og_description":"Have you ever noticed a principal_id of NULL in one of the catalog views? Discover how this might impact you and what the schema owner has to do with it.","og_url":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-01T16:00:30+00:00","article_modified_time":"2014-11-13T18:13:55+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/schema_with_specified_AUTHORIZATION.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\/2152\/the-mysterious-schema-owner\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Mysterious Schema Owner","datePublished":"2014-03-01T16:00:30+00:00","dateModified":"2014-11-13T18:13:55+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/"},"wordCount":566,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/schema_with_specified_AUTHORIZATION.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\/2152\/the-mysterious-schema-owner\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/","url":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/","name":"The Mysterious Schema Owner - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/schema_with_specified_AUTHORIZATION.jpg","datePublished":"2014-03-01T16:00:30+00:00","dateModified":"2014-11-13T18:13:55+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Have you ever noticed a principal_id of NULL in one of the catalog views? Discover how this might impact you and what the schema owner has to do with it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/schema_with_specified_AUTHORIZATION.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/schema_with_specified_AUTHORIZATION.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2152\/the-mysterious-schema-owner\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Mysterious Schema Owner"}]},{"@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-yI","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2152","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=2152"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2152\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}