change owner to multiple objects at the same time?

[lydiattc]

Hi,

I'm working on a legacy database and I need to change the owner of all the objects to some new user. How do I do it in a one-step procedure?

I've trie to use the following
"EXEC sp_changeobjectowner (select name from sysobjects), 'new_user'"
However, the query analyzer won't run it because of a syntax error. What is the correct syntax if I want to do something like this?

I know I could write a script with hundreds of lines and each line is one command of sp_changeobjectowner for one object. I wonder if there's any other way to do it.

In addition, after I change the owner, I need to give permission to all objects too. Here comes the same question. How do I give permissions to all objects at the same time.

(all objects include tables, views, and stored procedures)

Thanks,
Lydia

 

[ClaireHCM]

You should first find all the object name and their owner.Not all the objects belong to the same user.
Here is one way to find the object owner and object name

say you want to find what are the objects that dbo has

select a.uid,A.name,B.name as object_name from sysusers A ,sysobjects B where a.name = 'dbo'

when you use sp_changeobjectonwer always you need to specify the @objname ='owner.object' and @newowner