This post originated from an RSS feed registered with .NET Buzz by Darrell Norton. Original Post: Change all database object owners to dbo stored proc Feed Title: Darrell Norton's Blog Feed URL: /error.htm?aspxerrorpath=/blogs/darrell.norton/Rss.aspx Feed Description: Agile Software Development: Scrum, XP, et al with .NET Latest .NET Buzz Posts Latest .NET Buzz Posts by Darrell Norton Latest Posts From Darrell Norton's Blog

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