Friday, June 18, 2004

Change all database object owners to dbo stored proc

Change all database object owners to dbo stored proc

Nice utility SP.

"... proc to generate change owner scripts for all objects in a database not owned by dbo."

I've copied it here to ensure that I can find it in the future.
All rights, kudo's, congrants,etc belong to the author, Darell Norton.

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

1 comment:

Anonymous said...

Thanks a lot!!!! ;)