Changing ownership on all objects in a DB

Hi,
I have to change ownership of all the tables/storedprocedures/views in a
database.
Can some tell me a good and easy way to do it.
Thnx in advance.I don't know of anything easy.
Take a look at:
select * from information_schema.tables (tables and views)
select * from information_schema.routines (stored procedures)
You could,
open a cursor,
populate it with the names of the tables/views/stored procedures
loop through the cursor
use dynamic SQL to change ownership
Ugly...
Don't forget that you can 2 different objects with the same name and
different owners. This procedure will crash in this case.
Also, if you reference other objects from inside views and stored procedures
using the object owner, this does nothing to solve it.
One easier method to change view/stored procedure owners is to script out
all of them into a text file, do a search and replace on the owner and run
the script in Query Analyser. You could use this method to solde the
"referenced object owner" problem too. If you don't mind loosing all data in
your tables, you could use this method on tables.
For the tables, take a look at the "undocumented" stored procedure
sp_MSforeachtable. Google it for more details.
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:8C1ECABA-5302-4D34-93BF-015AF2A6916F@.microsoft.com...
> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.|||You might want to use the script given here >>
http://weblogs.asp.net/owscott/arch...1/30/65229.aspx
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"dotnettester" wrote:

> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.|||Well, you could start with this script, in Query Analyzer:
DECLARE @.newOwner SYSNAME;
SET @.newOwner = 'new_owner_name'; -- I assume you are changing to dbo?
IF USER_ID(@.newOwner) IS NOT NULL
BEGIN
SELECT 'EXEC sp_changeobjectowner
'''+QUOTENAME(name)+''','''+@.newOwner+''
''
FROM sysobjects
WHERE xtype IN ('P','U','IF','FN','TR','V')
AND uid != USER_ID(@.newOwner);
END
ELSE
RAISERROR('User %s does not exist.', 11, 1, @.newOwner);
This will yield a script in the bottom pane that you can copy and paste to
the top pane, syntax check, prune, and execute.
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:8C1ECABA-5302-4D34-93BF-015AF2A6916F@.microsoft.com...
> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.