Change all database object owners to dbo stored proc

I hate going back and changing the owner on objects to dbo. While this stored proc is useful, I don’t like having to find all the different user names and running it for each one. So I wrote this proc to generate change owner scripts for all objects in a database not owned by dbo.


Installation


Run the sql script to create the stored proc in whatever database you choose.  Prefix it with sp_ and put it in the master database if you expect to use it across several databases on the same SQL Server instance and you don’t want to duplicate it in each database (and you have permissions).


Usage


Type this in SQL Server Query Analyzer and run it:
ChangeAllObjectOwnersToDBO


In the results pane will be the text to change the owner of all non-dbo-owned objects to dbo. Copy all of the results text, paste in Query Analyzer main window, and run.  A warning message will be displayed if there is already an object of the same name owned by dbo.  You should resolve these problems on an individual basis.


You should see cautionary statements like this:
Caution: Changing any part of an object name could break scripts and stored procedures.
Don’t worry, that’s standard stuff. Hopefully you haven’t hard-coded a specific owner! If so, I don’t think this stored proc will help.


if exists (select * from sysobjects where id = object_id(N‘[dbo].[ChangeAllObjectOwnersToDBO]‘) and OBJECTPROPERTY(id, N‘IsProcedure’) = 1)
drop procedure [dbo].[ChangeAllObjectOwnersToDBO]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO


CREATE proc ChangeAllObjectOwnersToDBO
as
set nocount on


declare @uid int
declare @objName varchar(50)
declare @userName varchar(50)
declare @currObjName varchar(50)
declare @outStr varchar(256)
set @uid = user_id(‘dbo’)


declare chObjOwnerCur cursor static
for
select user_name(uid) as ‘username’, [name] as ‘name’ from sysobjects where uid <> @uid


open chObjOwnerCur
if @@cursor_rows = 0
begin
  print ‘All objects are already owned by dbo!’
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end


fetch next from chObjOwnerCur into @userName, @objName
while @@fetch_status = 0
begin
  set @currObjName = ‘dbo.’ + @objName
  if (object_id(@currObjName) > 0)
    print ‘WARNING *** ‘ + @currObjName + ‘ already exists ***’
  set @outStr = ‘sp_changeobjectowner ”’ + @userName + ‘.’ + @objName + ”’, ”dbo”’
  print @outStr
  print ‘go’
  fetch next from chObjOwnerCur into @userName, @objName
end


close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

26 Responses to Change all database object owners to dbo stored proc

  1. Darrell says:

    Geoff – and sweet script! It uses SQL like it’s supposed to be used (set-based processing), versus creating a cursor and iterating like a programming language. I didn’t ever bother to look at the script, just tweaked what was there to suit my requirements. :)

  2. Darrell says:

    Geoff – yep, anytime you hit sysobjects Microsoft absolves themselves of caring whether an upgrade breaks your stuff. You are supposed to use the INFORMATION_SCHEMA view (which is a SQL standard), but they don’t provide access to everything under the covers! Damned if you do, damned if you don’t. :)

  3. Hey Darrel,

    I was needing this exact thing yesterday. My boss at work had a script that did it, but i forgot to take it home with me, and i needed to run it at home :)

    So I used yours, and was happy, but i thought you might be interested in his, since it’s much much much shorter. It’s hard coded for dbo, but i imagine it’s easy to make that a param coming into an SP instead.

    Anyway, here tis.

    SELECT ‘EXEC(”sp_changeobjectowner @objname = ””’

    + ltrim(u.name) + ‘.’ + ltrim(s.name)

    + ”””

    + ‘, @newowner = dbo”)’

    FROM

    sysobjects s,

    sysusers u

    WHERE

    s.uid = u.uid

    AND u.name <> ‘dbo’

    AND xtype in (‘V’, ‘P’, ‘U’)

    AND u.name not like ‘INFORMATION%’

    order by

    s.name

    He did tell me it’s heavily reliant on knowing on how SQL7 and 200 work, and has no garantees it’ll work in yukon :)

  4. Darrell says:

    Try changing the variable declarations to this:

    declare @objName varchar(100)

    declare @userName varchar(100)

    declare @currObjName varchar(100)

    declare @outStr varchar(1000)

  5. Tomas says:

    Hey man, I’m having a little trouble. When I type "ChangeAllObjectOwnersToDBO" I copy the results and paste into new window. Everything is fine there, but when I run the commands, I get this:

    ———————————–

    sp_changeobjectowner ‘user939811.spCCTypeFetch’, ‘dbo’

    go

    Error: No objects owned by user939811.spCCTypeF

    ———————————–

    I looks like SQL Server chops off object name: instead of "user939811.spCCTypeFetch", I only get "user939811.spCCTypeF". Of course, the script fails. I know it’s not your fault, but do you know why SQL Server is behaving this way?

    Thanks a lot!

    Tomas

  6. Darrell says:

    Anytime Brendan! I’ll have some more good SQL stuff here soon.

  7. Yo D. Never told you how much I love this, but I’ve used it a bunch of times… You da man.

  8. Doug Sherman says:

    Thanks Darrell, worked like a charm!

  9. Shail says:

    Great ! saved a lot of our time

  10. Darrell says:

    Jeff – yes, actually I linked to it in my blog post and talked about what changes I made to improve it.

  11. jeff says:

    Ever read MS Article #275312???

  12. Darrell says:

    Enjoy!

  13. Mike says:

    Saved my butt, and lots of hours of my time. Thanks!!!

  14. Yusuff says:

    Awesome. Thank you.

  15. Shawn says:

    Thanks for this procedure.

    I have installed this on all my sql server instances.

  16. Darrell says:

    Glad you like it!

  17. Sunil says:

    This is awesome. A great timesaver !!!

    Thanks,

    Sunil

  18. Korivo says:

    Great thanx a lot!

    This is a great tool

  19. Darrell says:

    You run the stored proc to get a text output. Then copy the output to a NEW query analyzer window, and then run it. That should work.

  20. Korivo says:

    it seems that is doesnt rename the stored prodedure

    any idea why?

  21. Enrique says:

    Great post! Thanks.

  22. Dalavai says:

    This is Amazing man ,I already implemented in my production server

  23. mfDBA says:

    This is great! Thanks for sharing.

  24. Darrell says:

    Yeah, I had too!

  25. Mark says:

    Man, this is great! I can really put this to use right away on my current project. Wait, you already took care of it. :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>