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

20 Comments

  • 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.

  • Thanks for the script! It really helps!

  • This was a great script. Don't change anything except the oldOwner and you're good to go. Thanks for posting this script.

  • 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

  • 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.



  • 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.

  • 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??

  • 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.

  • 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

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

  • great article. thanks!

  • Great! Works just like a charm!

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

  • Now that is a slick script. Thanks much!

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

  • 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"

  • 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.

  • 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.

  • 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.

  • Great!!, It works perfect on SQL2008.

    Thanks

Comments have been disabled for this content.