Scott Forsyth's Blog

Postings on IIS, ASP.NET, SQL Server, Webfarms and general system admin.

Cloud Resources

IIS Resources

SQL Table Ownership Changes, Quick and Easy

A common task I run into is changing the ownership on a database table or stored procedure in SQL Server.  There are times when the owner is set incorrectly or differently what what I would like.

For example, if a table is created using 'CREATE TABLE Products' but I was logged in as 'scott' to the 'orcsweb' database, the owner would be 'scott' and the fully qualified object name would be 'orcsweb.scott.Products'.  This isn't always best.  In most cases I prefer 'dbo' as the owner since it makes queries easier when using mulitple users.

Of course if you created the table like so: 'CREATE TABLE dbomultiple.Products' then the owner will be 'dbo' but in the case where this was created wrong in the first place how do you change this afterward?

The manual way if you only have a couple tables or objects to change is to use sp_changeobjectowner.  This is great and what I use many times.

But, what about the times when you have quite a few to change and doing it manually isn't reasonable or desirable?  David Penton, Microsoft MVP - SQL Server, has put together a great SQL script that I think everyone should keep handy.  (Yes, I did get his permission to post this)

 

 

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
  @old = 'oldOwner_CHANGE_THIS'
  , @new = 'dbo'
  , @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @old + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

 

-------------------------------

The same can be done to stored procedures.  This example works differently though.  It doesn't actually make the change.  Save the results in text and then paste the result back into Query Analyzer and run it. 

-------------------------------

DECLARE @oldOwner sysname, @newOwner sysname

SELECT
    @oldOwner = 'oldOwner_CHANGE_THIS'
    , @newOwner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
    INFORMATION_SCHEMA.ROUTINES a
where
    a.ROUTINE_TYPE = 'PROCEDURE'
    AND a.SPECIFIC_SCHEMA = @oldOwner
    AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0

Posted: Jan 30 2004, 12:59 PM by OWScott | with 21 comment(s) |
Filed under:

Comments

Confused DBA said:

I thought the quote signs were messed up so I tried various combination of single quotes and double quotes. Don't experiment. Just copy and paste.
# March 18, 2004 5:05 PM

Marti Ryan said:

Thanks for the script! It really helps!
# May 19, 2004 7:42 AM

Chris Bowen said:

This was a great script. Don't change anything except the oldOwner and you're good to go. Thanks for posting this script.
# June 7, 2004 7:07 PM

Kenneth Rainey said:

Thanks for posting and thanks to the author for sharing. Here's a quick mod if you need to run this for stored procedures...

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old = 'oldOwner'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM information_schema.routines
WHERE
QUOTENAME(ROUTINE_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) = ''?''
AND ROUTINE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql
# June 30, 2004 9:40 AM

Kenneth Rainey said:

Sorry... the sp_MSforeachtable doesn't have an equivalent for stored procedure objects. You'll have to create your own iteration loop to get it to work. Feel free to delete earlier post.

# June 30, 2004 10:36 AM

Scott Forsyth said:

Kenneth, thanks for the post. I'll get the script for stored procedures and add it here. I'll remove your post then too. David Penton will have something for this, I'm sure.
# June 30, 2004 4:02 PM

Hesky said:

Could we also use this script if users are using the table?? SO what i want i to change the ownership when also the users are using this table in the application. Is there a possiblity drop all users which are connecting to these table??
# July 26, 2004 2:36 PM

Scott Forsyth said:

Hesky. I can't answer that right off. You can do it from Enterprise Manager by starting the process of detaching a database and killing all sessions. But from SQL directly, I would have to look it up. I don't know right off.
# August 3, 2004 6:56 PM

Darran said:

I have adjusted the code slightly for a SQL 2005 version. I'm not a SQL expert, so I'd welcome comments if this code will work as I'd expect.

DECLARE @old sysname, @sql varchar(1000)

SELECT

 @old = 'oldOwner_CHANGE_THIS'

 , @sql = '

 IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

 WHERE

     QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

     AND TABLE_SCHEMA = ''' + @old + '''

 )

 ALTER SCHEMA dbo TRANSFER ?'

EXECUTE sp_MSforeachtable @sql

# November 30, 2006 6:53 AM

Rick T said:

Thanks for the tip on the system sproc Scott.  Most helpful!

# January 2, 2007 3:52 PM

soumya said:

great article. thanks!

# February 2, 2007 6:55 AM

AZ said:

Great! Works just like a charm!

# March 10, 2007 5:36 AM

Shridhar said:

Saved my day yesterday!  I had to tweak it a bit to work for views ... no problems.

# April 5, 2007 10:01 AM

Bill S said:

Now that is a slick script.  Thanks much!

# June 5, 2007 10:16 AM

Sam C said:

Life saver! + Thanks for the 2005/2008 tweak.

# February 18, 2011 2:13 AM

Jesse Garza said:

How to I change ownership on tables under the "view" dorectory, currently they are own by "User123" and I want them to be own by "dbo"

# March 8, 2011 9:38 AM

OWScott said:

Hi Jesse,

You can do the same for Views using the following:

DECLARE @oldOwner sysname, @newOwner sysname

SELECT

   @oldOwner = 'OLD_OWNER_NAME'

   , @newOwner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)+''','''+@newOwner+''''

from

   INFORMATION_SCHEMA.VIEWS a

where

   a.TABLE_SCHEMA = @oldOwner

   AND

OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)), 'IsMSShipped') = 0

Make sure to follow the instructions for the 2nd query above.  Basically this just creates the SQL command for you, so you need to copy and paste and run it.

# March 8, 2011 12:41 PM

Mike said:

Thanks for posting this script!  It was a real time saver!  

# May 25, 2011 8:17 AM

Larry Rebich said:

I have been looking for a way to do this for months. It work on my Go Daddy server where they seem to want to use other that dbo for DB owners.

# April 22, 2012 11:51 PM

Lenny Haacker said:

The script worked to update Sql2008R2.

Just goes to show how much a basic function is not used...then when you need it.  Thanks Scott & MS.

# April 27, 2012 11:21 AM

Adrian said:

Great!!, It works perfect on SQL2008.

Thanks

# May 30, 2013 2:47 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)