{"id":1532,"date":"2012-02-03T19:46:05","date_gmt":"2012-02-03T19:46:05","guid":{"rendered":"http:\/\/craftydba.com\/?p=1532"},"modified":"2017-10-12T11:57:14","modified_gmt":"2017-10-12T11:57:14","slug":"database-object-ownership","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1532","title":{"rendered":"Database & Object Ownership"},"content":{"rendered":"

The most ANNOYING<\/span> thing is to come across a database that is still owned by the user that created it. In prior server versions, this was a big deal since users owned objects and ownership had to be changed before the user could be removed from the system. Therefore, if user named ford created a table named brands, the fully qualified table name would be ford.brands. <\/p>\n

In SQL Server 2005 and greater, this has changed with a default schema of dbo. All objects created in this schema are owned by dbo. <\/p>\n

Today I am going to talk about how to use the ALTER AUTHORIZATION<\/a> command to take ownership of databases, schemas or objects.<\/p>\n

I am going to redesign my simple AUTOS sample database with a schema called USA and a table called BRANDS.<\/p>\n

The SQL snipet below will setup the database for my example for usage.<\/p>\n

\r\n-- Start with master database\r\nUSE MASTER;\r\nGO\r\n\r\n-- Create a autos database\r\nCREATE DATABASE AUTOS;\r\nGO\r\n\r\n-- Use the database\r\nUSE [AUTOS]\r\nGO\r\n\r\n-- Create a USA schema\r\nCREATE SCHEMA USA AUTHORIZATION dbo;\r\nGO\r\n\r\n-- Create a BRANDS table\r\nCREATE TABLE USA.BRANDS\r\n(\r\nMyId INT PRIMARY KEY CLUSTERED,\r\nMyValue VARCHAR(20)\r\n)\r\nGO\r\n\r\n-- Load the table with data\r\nINSERT INTO USA.BRANDS (MyId, MyValue) VALUES\r\n(1, 'Continental'),\r\n(2, 'Edsel'),\r\n(3, 'Lincoln'),\r\n(4, 'Mercury'),\r\n(5, 'Ram')\r\nGO\r\n<\/pre>\n<\/p>\n
At this point, the database is defaulted to my network id.  This is not good.  If my id was deactivated, access to the database will be blocked until ownership is changed.<\/p>\n
As a rule, I ALWAYS<\/span> change the ownership of the database back to SA after creation.  The ALTER AUTHORIZATION<\/a> statement allows us to specify the database we want to change and the owner to assign to it.<\/p>\n
\r\n-- Switch owner to system admin\r\nALTER AUTHORIZATION ON DATABASE::AUTOS TO SA;\r\nGO\r\n<\/pre>\n
\"\"<\/a>\"\"<\/a><\/p>\n
Sometimes it is neccessary to create an application user or application role that owns the schema.  This way, schema objects can be seperated from different applications but reside in the same database.<\/p>\n
Code below adds a login and user named [FORD_USER].  The USA schema has ownership transfered from dbo to [FORD_USER].<\/p>\n
\r\n-- Add new login.\r\nCREATE LOGIN [FORD_USER] WITH PASSWORD=N'F0e2by02x12#', DEFAULT_DATABASE=[AUTOS]\r\nGO\r\n\r\n-- Add new user.\r\nCREATE USER [FORD_USER] FOR LOGIN [FORD_USER] WITH DEFAULT_SCHEMA=[USA]\r\nGO\r\n\r\n-- Switch owner to new user\r\nALTER AUTHORIZATION ON SCHEMA::USA TO [FORD_USER];\r\nGO\r\n<\/pre>\n<\/p>\n
The before and after images shows that the schema being owned by DBO and FORD_USER respectively.<\/p>\n
\"\"<\/a>\"\"<\/a><\/p>\n
Like any real life example, a business requirement is to have a BRAND NAMES view which is created in the USA schema.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
\r\n-- Create a view on brands\r\nCREATE VIEW USA.BRAND_NAMES AS \r\nSELECT TOP 100 B.MyValue FROM USA.BRANDS B\r\nORDER BY B.MyValue\r\nGO\r\n\r\n-- USA Schema\r\nSELECT * FROM [AUTOS].[USA].[BRAND_NAMES]\r\nGO\r\n<\/pre>\n<\/p>\n
I am going to create a new schema called CAN for a new country (CANADA) which is being added to the database.<\/p>\n
\r\n-- Create a CANADA schema\r\nCREATE SCHEMA CAN AUTHORIZATION dbo;\r\nGO\r\n\r\n-- Move object from one schema to another\r\nALTER SCHEMA CAN TRANSFER USA.BRAND_NAMES\r\n\r\n-- Who ownes the object?\r\nSELECT name, object_id, principal_id FROM sys.objects o WHERE o.type = 'V'\r\n\r\n-- Database roles & users\r\nSELECT p.name, p.principal_id FROM sys.database_principals p\r\n\r\n-- dbo is curent owner of CAN\r\nALTER AUTHORIZATION ON OBJECT::CAN.BRAND_NAMES TO SCHEMA OWNER;\r\nGO\r\n<\/pre>\n<\/p>\n
At this point, we have no access to the view since it is part of the CAN schema owned by dbo.  In fact, we can not even see the view in the object explorer.   <\/p>\n
\"\"<\/a>
\n\"\"<\/a><\/p>\n
Either giving FORD_USER rights to the schema or the object allows us to access the view.<\/p>\n
\r\n-- Change object owner to ford user\r\nALTER AUTHORIZATION ON OBJECT::CAN.BRAND_NAMES TO [FORD_USER];\r\nGO\r\n\r\n-- or switch schema owner to ford user\r\nALTER AUTHORIZATION ON SCHEMA::CAN TO [FORD_USER];\r\nGO\r\n\r\n-- CAN Schema\r\nSELECT * FROM [AUTOS].[CAN].[BRAND_NAMES]\r\n<\/pre>\n<\/p>\n
As you can see, we now can select data from the view.<\/p>\n
\"\"<\/a><\/p>\n
To recap the talk, at a minimum PLEASE<\/span> make sure that the database owned by SA and the default schema is owned by dbo.   Objects can be created in various schemas to implment least amount of privledges for users, a common security tactic for DBA’s.  If you are unfortunately like me to have  existing SQL Server 2000 systems, use the sp_changedbowner<\/a> and sp_changeobjectowner<\/a> to manage ownership.<\/p>\n","protected":false},"excerpt":{"rendered":"
The most ANNOYING thing is to come across a database that is still owned by the user that created it. In prior server versions, this was a big deal since users owned objects and ownership had to be changed before the user could be removed from the system. Therefore, if user named ford created a table named brands, the fully qualified table name would be ford.brands. In SQL Server 2005 and greater, this has changed with a default schema of dbo. All objects created in this schema are owned by…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[344,345,348,12,15,346,347,343,342,28,29],"class_list":["post-1532","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-alter-authorization","tag-database-ownership","tag-dbo","tag-free-code","tag-john-f-miner-iii","tag-object-ownership","tag-sa","tag-sp_changedbowner","tag-sp_changeobjectowner","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1532","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1532"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1532\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1532"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1532"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1532"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}