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 dbo.
Today I am going to talk about how to use the ALTER AUTHORIZATION command to take ownership of databases, schemas or objects.
I am going to redesign my simple AUTOS sample database with a schema called USA and a table called BRANDS.
The SQL snipet below will setup the database for my example for usage.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- Start with master database USE MASTER; GO -- Create a autos database CREATE DATABASE AUTOS; GO -- Use the database USE [AUTOS] GO -- Create a USA schema CREATE SCHEMA USA AUTHORIZATION dbo; GO -- Create a BRANDS table CREATE TABLE USA.BRANDS ( MyId INT PRIMARY KEY CLUSTERED, MyValue VARCHAR(20) ) GO -- Load the table with data INSERT INTO USA.BRANDS (MyId, MyValue) VALUES (1, 'Continental'), (2, 'Edsel'), (3, 'Lincoln'), (4, 'Mercury'), (5, 'Ram') GO |
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.
As a rule, I ALWAYS change the ownership of the database back to SA after creation. The ALTER AUTHORIZATION statement allows us to specify the database we want to change and the owner to assign to it.
|
1 2 3 |
-- Switch owner to system admin ALTER AUTHORIZATION ON DATABASE::AUTOS TO SA; GO |
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.
Code below adds a login and user named [FORD_USER]. The USA schema has ownership transfered from dbo to [FORD_USER].
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Add new login. CREATE LOGIN [FORD_USER] WITH PASSWORD=N'F0e2by02x12#', DEFAULT_DATABASE=[AUTOS] GO -- Add new user. CREATE USER [FORD_USER] FOR LOGIN [FORD_USER] WITH DEFAULT_SCHEMA=[USA] GO -- Switch owner to new user ALTER AUTHORIZATION ON SCHEMA::USA TO [FORD_USER]; GO |
The before and after images shows that the schema being owned by DBO and FORD_USER respectively.
Like any real life example, a business requirement is to have a BRAND NAMES view which is created in the USA schema.
|
1 2 3 4 5 6 7 8 9 |
-- Create a view on brands CREATE VIEW USA.BRAND_NAMES AS SELECT TOP 100 B.MyValue FROM USA.BRANDS B ORDER BY B.MyValue GO -- USA Schema SELECT * FROM [AUTOS].[USA].[BRAND_NAMES] GO |
I am going to create a new schema called CAN for a new country (CANADA) which is being added to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Create a CANADA schema CREATE SCHEMA CAN AUTHORIZATION dbo; GO -- Move object from one schema to another ALTER SCHEMA CAN TRANSFER USA.BRAND_NAMES -- Who ownes the object? SELECT name, object_id, principal_id FROM sys.objects o WHERE o.type = 'V' -- Database roles & users SELECT p.name, p.principal_id FROM sys.database_principals p -- dbo is curent owner of CAN ALTER AUTHORIZATION ON OBJECT::CAN.BRAND_NAMES TO SCHEMA OWNER; GO |
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.
Either giving FORD_USER rights to the schema or the object allows us to access the view.
|
1 2 3 4 5 6 7 8 9 10 |
-- Change object owner to ford user ALTER AUTHORIZATION ON OBJECT::CAN.BRAND_NAMES TO [FORD_USER]; GO -- or switch schema owner to ford user ALTER AUTHORIZATION ON SCHEMA::CAN TO [FORD_USER]; GO -- CAN Schema SELECT * FROM [AUTOS].[CAN].[BRAND_NAMES] |
As you can see, we now can select data from the view.
To recap the talk, at a minimum PLEASE 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 and sp_changeobjectowner to manage ownership.
Wow! Thank you! I continually wanted to write on my website something like that. Can I implement a portion of your post to my blog?
— Please see the copyright rules. A portion can be copied; However credit should be given to the Crafty DBA.