-
changing the object owner on tables
What is the best way to change the objectowner if you have over 50 tables to change? Thanks for your help.
-
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.
-
--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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|