Introduction
Object ownership is often confusing for new DBAs and developers. You would think that tables, views, and stored procedures created by a member of the db_owner database role would be owned by dbo by default, but that isn’t necessarily the case. Recently this issue has cropped up several times at the firm where I work.
This article covers whether database objects will be owned by dbo or another account in SQL 2000, and gives you a script you can use to change the owner of hundreds or thousands of tables at once.
Note that the SQL Server name “owner” maps to the SQL-92 name “schema”. I’ll use the term “owner” throughout this article. I am also
using table creation in my examples, but the concept applies to other object types as well.
Just the facts
Before I get too deep trying to explain how this works, here is a summary of what to expect.
| Server Role | Database Role or Permission | Login | Table Creation Method | Owner |
|---|---|---|---|---|
| sysadmin | -- | sa | Enterprise manager | dbo |
| sysadmin | -- | sa | create table [test1](abc int) | dbo |
| sysadmin | -- | sa | create table [fred].[test1](abc int) | fred |
| -- | db_owner | sam | Enterprise Manager | dbo |
| -- | db_owner | sam | create table [test2](abc int) | sam |
| -- | db_owner | sam | create table [dbo].[test2](abc int) | dbo |
| -- | db_owner | sam | create table [fred].[test2](abc int) | fred |
| -- | create table permission | fred | Enterprise Manager | fred |
| -- | create table permission | fred | create table [test3](abc int) | fred |
| -- | create table permission | fred | Create table [dbo].[test3](abc int) | ERROR |
| -- | owns the database | sue | Enterprise Manager | dbo |
| -- | owns the database | sue | create table [test4](abc int) | dbo |
| -- | owns the database | sue | create table [fred].[test4](abc int) | fred |
Basically, an object created by an account operating with sa rights or the owner of the database defaults to
dbo ownership. The confusion begins when an object is created with a script by a member of the db_owner role without explicitly specifying dbo as the owner.
Fred can't create a table with the dbo owner at all since he isn't a member of the db_owner role.
Why you should care
When a user presents a statement to SQL without specifying the owner of an object, SQL first looks for an object owned by the user. If one is not found, then SQL looks for an object owned by dbo.
If our friend Fred tries to query test2, he will actually retrieve the rows from [fred].[test2]. Assuming Fred has permission to query [dbo].[test2], he must qualify the table name with the owner.
He will need to query [dbo].[test2] to see the same results that any of the other users will see. Obviously, this could get messy if the software doesn’t specify ownership in its T-SQL statements, and objects are owned by multiple users, usually by accident.
Once a user is the owner of one or more objects, the user can not be removed from the database without transferring ownership to another account.
Real life example one
In our shop there are packaged software applications that play scripts during installation or maintenance that have caused some problems. Our trouble-ticket software drops and recreates tables when the administrator adds new functionality or fields. I found that making the account he uses for this task a member of the db_owner role caused all of the new objects to be owned by that account. If that happens, the trouble-ticket software breaks because the tables can not be found by the account used to run the software. To remedy that problem, I made the account the owner of the database and instructed the administrator to be sure to log in with that account when applying updates.
To change the owner of the database and map the account to dbo, I used a script like this:
use troubleSoftwareDB go sp_dropuser 'softwareAdmin' sp_changedbowner 'softwareAdmin'
It is also possible to use the sp_addalias command to map the account to dbo. This is not recommended, however, it is only provided for backward compatibility.
Real life example two
Another software package used in the law firm where I work expects the objects to be owned by a certain account. This particular software creates a database for each legal case. Every once in a while, the administrator of this software calls and says that they accidentally used the wrong account when setting up a new database and all of the objects are owned incorrectly. Since there are hundreds of tables, and I hate doing tedious work, I wrote a script to rename the objects. Now, when I get that phone call, the problem can be solved in minutes!
Script to change multiple table owners
This script will generate a second script that will change the owner of all tables that are owned by an account that you specify. You must be a member of the sysadmin server role, the db_owner database role, or a member of both the ddl_admin and ddl_securityadmin database roles to change object owners.
- In Query Analyzer, change the “Execute Mode” to “Results in Text”. CTRL + T.
- Make sure you are connected to the correct database – the one whose objects you wish to change.
- Copy the script below and paste into Query Analyzer.
declare @OldOwner varchar(100) declare @NewOwner varchar(100) set @OldOwner = 'OldOwner' set @NewOwner = 'NewOwner' select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + ''' go' from information_schema.tables where Table_schema = @OldOwner
- Change the values of @OldOwner and @NewOwner to fit your situation.
- Play the script.
- You results should looks something like this:
sp_changeobjectowner '[OldOwner].[table1]', 'NewOwner' go sp_changeobjectowner '[oldOwner].[table2]', 'NewOwner' go
- Copy the resulting script into a Query Analyzer window connected to the correct database.
- Play the resulting script to change the owners.
Conclusion
Keep in mind that objects may be owned by users other than dbo when you least expect it. Be sure to explicitly state the owner when creating scripts. If a software package install creates objects in a database, you may want to run the installation with an account with elevated privileges.
