Search This Blog

Changing the Ownership of all the Objects in a Database

Here are some methods to change the ownership of all the objects in a database

1. The first methos uses the conventional CURSORS

DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN ('V', 'P', 'U')
ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''dbo''')
FETCH NEXT FROM CurDbObjects INTO @dbObject END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects



2) The second method uses the undocumented sp_MsForEachTable procedure

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 ANDOBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0

No comments: