Menu
I can change the ownership of a single table using sp_changeobjectowner.
If I want to change ownership of all the objects in a database, should I write a stored procedure to iterate through each object or is there another way?
UPDATE I also found that changing the default schema for my user solved the issue that was causing me to think I needed to change ownership of all the objects.
336
Go to SQL Server Management Studio >> Right Click on the Database >> Go to Properties >> Go to Files and select OWNER. You can see the following screenshot which describes how to do the same task.
If you can query the tablenames in your schema, you can generate the queries to ALTER table ownership. For example: select 'ALTER TABLE ' || t. tablename || ' OWNER TO new_owner;' from pg_tables t where t.
Answer: The traditional approach for changing a table owner is to use CTAS or export/import. create table bill. emp as select * from scott.
Right-click the object in the Studio Tree and select Admin Change Owner. The Change Owner dialog appears. Select the new owner from the dropdown list. Save your changes.
Try running this query and then just select all results and execute in separate query
select 'EXEC sp_changeobjectowner ''' + S.name + '.' + O.name + '' + ''', ''new_owner'''
from sys.all_objects O
inner join sys.schemas S
on O.schema_id = S.schema_id
where O.type in ('FN','IF','P','TF','U','V', 'TT', 'TF')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
