Change the owner of all Database 0bjects

Vuyiswamb
Posted by Vuyiswamb under Sql Server category on | Points: 40 | Views : 1351
CREATE PROC spx_ChangeDatabase_Ownership
(
@currentOwner varchar(max),
@newOwner varchar(max)
)
as

DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)


DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects
WHERE
xtype in ('FN','IF','P','TF','U','V', 'TT', 'TF') --Modify list to add and remove object types*
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE @@FETCH_STATUS = 0
BEGIN
SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
EXEC sp_changeobjectowner @qualifiedObject, @newOwner
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor

Comments or Responses

Login to post response