Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Understanding Object Ownership

By Kathi Kellenberger, (first published: 2005/07/18)

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 RoleDatabase Role or Permission LoginTable Creation MethodOwner
sysadmin--saEnterprise managerdbo
sysadmin--sacreate table [test1](abc int)dbo
sysadmin--sacreate table [fred].[test1](abc int)fred
--db_ownersamEnterprise Managerdbo
--db_ownersamcreate table [test2](abc int)sam
--db_ownersamcreate table [dbo].[test2](abc int)dbo
--db_ownersamcreate table [fred].[test2](abc int)fred
--create table permissionfredEnterprise Managerfred
--create table permissionfredcreate table [test3](abc int)fred
--create table permissionfredCreate table [dbo].[test3](abc int)ERROR
--owns the databasesueEnterprise Managerdbo
--owns the databasesuecreate table [test4](abc int)dbo
--owns the databasesuecreate 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.

Total article views: 50750 | Views in the last 30 days: 82
 
Related Articles
FORUM

Database owner

Database owner

FORUM

Database Owner

Database Owner and db_owner

FORUM

How to change object owner......

Change Object Owner

FORUM

scripting a database without db owner permissions

scripting a database without db owner permissions

BLOG

A Script A Day - Day 5 - Database Owner Permissions

Today's script will list all principals with membership in the db_owner fixed database role. /*...

Tags
advanced    
database design    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones