{"id":2180,"date":"2014-03-04T11:00:20","date_gmt":"2014-03-04T16:00:20","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2180"},"modified":"2014-11-13T13:12:57","modified_gmt":"2014-11-13T18:12:57","slug":"default-owner","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2180\/default-owner\/","title":{"rendered":"Who is the Default Owner of your Database and Server Objects?"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nEvery securable in SQL Server has an owner. Well, almost every securable: Logins and users cannot have a user specified, but you could consider them as being owned by themselves.\n<\/p>\n

\nToday I would like to look at, what happens if you do not explicitly specify an owner during or after the creation of a securable.\n<\/p>\n

Creating a Schema with Default Owner<\/h3>\n

\nBefore we can dive right into creating schemata, we first need a few principals. For that, we can use the following SQL batch:\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;
\nCREATE LOGIN TestLogin3 WITH PASSWORD='********',CHECK_POLICY=OFF;
\nCREATE USER TestUser3 FROM LOGIN TestLogin3;
\nCREATE LOGIN TestLogin4 WITH PASSWORD='********',CHECK_POLICY=OFF;
\nCREATE LOGIN TestLogin5 WITH PASSWORD='********',CHECK_POLICY=OFF;
\n[\/sql]\n<\/div>\n

\nThere are five logins and three users created. You will see later why two of the logins did not get a user associated with them.\n<\/p>\n

\nNow let us use TestLogin1<\/span> to create a schema:\n<\/p>\n

\n[sql]\nGRANT ALTER ANY SCHEMA TO TestUser1;
\nGO
\nEXECUTE AS LOGIN='TestLogin1';
\nGO
\n CREATE SCHEMA TestSchema1;
\nGO
\nREVERT
\n[\/sql]\n<\/div>\n

\nThe GRANT<\/span> statement grants the required permissions to create a schema in the database. The EXECUTE AS<\/span> than switches the security context to TestLogin1<\/span> before creating the schema TestSchema1<\/span>.\n<\/p>\n

\nThe next step then is to check which principal ended up being the owner of that newly created schema. We can use this query for that:\n<\/p>\n

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

\nNot totally unexpected, the executing principal ended up being the owner of the new schema:\n<\/p>\n

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

\nTo be exact, the owner is not TestLogin1<\/span> but the associated user in the database, TestUser1<\/span>.\n<\/p>\n

\nIt makes sense for the creator to also be the owner of a securable. However, when you look at a real life system, most database securables are owned by dbo<\/span>. Let us try to figure out why that happens.\n<\/p>\n

\nMaybe granting CONTROL<\/span> on the database to the creator will change this behavior? Let's try:\n<\/p>\n

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

\nNo luck there, the creator is still the owner. dbo<\/span> stands for \"database owner\", so maybe adding the creator to the db_owner<\/span> role will change the behavior?\n<\/p>\n

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

\nAgain, no luck. But the actual database owner should work.\n<\/p>\n

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

\nIt did, or did it? dbo<\/span> is actually a standard user in the database and it has an associated login. That login is, you guessed it, the database owner:\n<\/p>\n

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

\nSo while securables created by the database owner actually are owned by dbo<\/span>, this is still the user that created them. Also, most objects are probably not created by the database owner itself, so this still does not explain why most securables end up with dbo<\/span> as owner. Let us go one step further and look at members of the sysadmin<\/span> fixed server role:\n<\/p>\n

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

\nAnd here we have a good explanation. Most members of the sysadmin<\/span> fixed server role do not have an associated user in each database. However, the owner of a securable with database scope has to be a user, so it makes sense for SQL Server to default the owner to dbo<\/span>, a user that always exists, when such a securable is created by a sysadmin<\/span>. Taking into account, that most create scripts are probably executed by a sysadmin<\/span>, you would expect to find dbo<\/span> being the most prevalent owner.\n<\/p>\n

The Default Owner for Server Scope Securables<\/h3>\n

\nThere is no dbo<\/span> concept for server scope securables. They are always owned by the login that created them, no matter of any server roles that the login might be a member of. That behavior can lead to problems if the owner is a windows login, particularly if the newly created securable is a database. It is advisable to always change the owner of a server scope securable to a SQL Login like SA<\/span>. This is possible even if SQL authentication is disabled on the instance.\n<\/p>\n

Summary<\/h3>\n

\nIn most circumstances, the owner of a newly created securable is the principal executing the create statement (or their associated user in the database). However, if a member of the sysadmin<\/span> fixed server role creates a database scope securable like a schema, the owner is set to dbo<\/span>.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default? 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":[59,126,125,50,38,58,56,15,127],"class_list":["post-2180","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-database-principals","tag-dbo","tag-owner","tag-permission","tag-security-2","tag-security-management","tag-server-principals","tag-sql-server","tag-sysadmin"],"acf":[],"yoast_head":"\nWho is the Default Owner of your Database and Server Objects? - sqlity.net<\/title>\n<meta name=\"description\" content=\"When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default?\" \/>\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\/2180\/default-owner\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Who is the Default Owner of your Database and Server Objects? - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2180\/default-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-04T16:00:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:12:57+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.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\\\/2180\\\/default-owner\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Who is the Default Owner of your Database and Server Objects?\",\"datePublished\":\"2014-03-04T16:00:20+00:00\",\"dateModified\":\"2014-11-13T18:12:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/\"},\"wordCount\":792,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg\",\"keywords\":[\"database principals\",\"dbo\",\"owner\",\"Permission\",\"security\",\"security management\",\"server principals\",\"SQL Server\",\"sysadmin\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/\",\"name\":\"Who is the Default Owner of your Database and Server Objects? - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg\",\"datePublished\":\"2014-03-04T16:00:20+00:00\",\"dateModified\":\"2014-11-13T18:12:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default?\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2180\\\/default-owner\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Who is the Default Owner of your Database and Server Objects?\"}]},{\"@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":"Who is the Default Owner of your Database and Server Objects? - sqlity.net","description":"When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default?","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\/2180\/default-owner\/","og_locale":"en_US","og_type":"article","og_title":"Who is the Default Owner of your Database and Server Objects? - sqlity.net","og_description":"When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default?","og_url":"https:\/\/sqlity.net\/en\/2180\/default-owner\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-04T16:00:20+00:00","article_modified_time":"2014-11-13T18:12:57+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.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\/2180\/default-owner\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Who is the Default Owner of your Database and Server Objects?","datePublished":"2014-03-04T16:00:20+00:00","dateModified":"2014-11-13T18:12:57+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/"},"wordCount":792,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg","keywords":["database principals","dbo","owner","Permission","security","security management","server principals","SQL Server","sysadmin"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2180\/default-owner\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/","url":"https:\/\/sqlity.net\/en\/2180\/default-owner\/","name":"Who is the Default Owner of your Database and Server Objects? - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg","datePublished":"2014-03-04T16:00:20+00:00","dateModified":"2014-11-13T18:12:57+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"When a new securable is created and an owner is not specified, a default owner is set. What server or database principal is used for the owner by default?","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2180\/default-owner\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/SCHEMA_created_by_user_with_ALTER-ANY-SCHEMA_permission.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2180\/default-owner\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Who is the Default Owner of your Database and Server Objects?"}]},{"@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-za","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2180","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=2180"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2180\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}