changing the object owner on tables

[stinkfist]

What is the best way to change the objectowner if you have over 50 tables to change? Thanks for your help.

[skhanal]

You can use sp_changeobjectowner stored procedure.


But before you run it for each table generate scripts to assign permissions to the tables. You can do it from Enterprise Manager, right click on the table, go to all tasks and generate scripts. On options tab select script object-level permissions.

After you change the owner, run script to grant permissions.

[MAK]

--This will generate a series of statements which can be used to change all the object's ownership to dbo

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
ltrim(u.name) + '.' + ltrim(s.name) + '''''' + ', @newowner = dbo'')' +
char(13)+char(10)+'Go'+char(13)+char(10)
FROM sysobjects s, sysusers u
WHERE s.uid = u.uid AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND u.name not like 'INFORMATION%' order by s.name