Monday, November 29, 2010

Change object owner in SQL Server 2005

EXEC sp_changeobjectowner 'web.rego_for_dism', 'dbo'

------

The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It uses sp_changeobjectowner system stored procedure:



DECLARE tabcurs CURSOR
FOR
SELECT 'SOMEOWNER.' + [name]
FROM sysobjects
WHERE xtype = 'u'

OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0
BEGIN

EXEC sp_changeobjectowner @tname, 'dbo'

FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs

No comments:

Post a Comment