Change Object owners


sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

exec sp_changeobjectowner 'table name', 'dbo'

How to change ownership of ALL tables in a db to ‘dbo’ with TSQL?

출처(Original Post) : Not Remember
Test or Not : Not yet

The code below will generate the commands needed; user would need to copy them back into Query Analyzer code section and run.

If instead you want to use dynamic SQL to exec them directly, let me know:


DECLARE @newOwner VARCHAR(60)

SET @newOwner = 'dbo'

SELECT 'EXEC sp_changeObjectOwner ''' + USER_NAME(uid) + '.' +
CAST(name AS VARCHAR(200)) + ''', ' +
'''' + @newOwner + '''' +
' --' + CASE
WHEN xtype = 'U' THEN 'table'
WHEN xtype = 'V' THEN 'view'
WHEN xtype = 'P' THEN 'stored proc'
ELSE 'function' END
FROM sysobjects WITH (NOLOCK)
WHERE xtype IN ('U') --(, 'V', 'FN', 'IF', 'TF', 'P')
--AND uid = USER_ID(@currOwner)
AND name NOT LIKE 'sys%'
AND uid <> 1
ORDER BY name