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'''
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
To change table owners, modify the query and repeat the proces:
SELECT 'EXEC sp_changeobjectowner ''dbo.' + TABLE_NAME + ''', ''thenewuser'''
Sunday, 16 January 2005 18:37:40 (GMT Standard Time, UTC+00:00) Database