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