.Net ramblings
# Sunday, 16 January 2005
SQL - change object owner for multiple objects

Tibor Karaszi showed how to do this time saving technique on a newsgroup post. You can use the sp_changeobjectowner stored procedure in the Master database to change the owner of an object one at a time, but there is a semi-automatic way to do it.

Open query analyser, change the selected database to the on you want to work on. The following query generates a list of exec commands for all the sprocs and UDFs in the database:


SELECT 'EXEC sp_changeobjectowner ''dbo.' + ROUTINE_NAME + ''', ''thenewuser'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'

Execute it, then click the top left corner of the output window, to select all the rows. Copy and paste the rows into a new query window and execute.

To change table owners, modify the query and repeat the proces:

SELECT 'EXEC sp_changeobjectowner ''dbo.' + TABLE_NAME + ''', ''thenewuser'''
FROM INFORMATION_SCHEMA.TABLES

Sunday, 16 January 2005 18:37:40 (GMT Standard Time, UTC+00:00)  #    Comments [2]  Database

Thursday, 14 July 2005 14:07:20 (GMT Daylight Time, UTC+01:00)
Hey, just found this browsing around. really helped me save time from changing the table owner one at a time.

Thanks
Monday, 22 October 2007 15:53:54 (GMT Daylight Time, UTC+01:00)
Thanks a ton for this, saved me a bunch of time.
Josh
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview