Script to change all database object owners at one time

Hi:
Does any one know where I can find a script that will change ownership of
all database objects at once. I took over control of a large database and
changing owners using sp_changeobjectowner one at a time will take forever.
Thanks,
CharlieYou could use a cursor to retrieve the objects that you want to change
(tables, views, stored procedures, user defined functions...) and call
sp_changeobjectowner on each object.
You can find examples of cursors within Books Online.
Keith
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||Since this is a one time thing, look at the UNdocumented stored procedure
sp_MSforeachtable.
This will give you an idea on how to use it:
http://www.databasejournal.com/feat...cle.php/3441031
http://www.dbazine.com/sql/sql-articles/larsen5
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||This will generate the script for you for all user-defined views, tables,
stored procedures and functions that aren't already owned by dbo:
SELECT 'EXEC sp_changeobjectowner
'''+TABLE_SCHEMA+'.'+TABLE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),
'IsMsShipped')=0
AND TABLE_SCHEMA != 'dbo'
SELECT 'EXEC sp_changeobjectowner
'''+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
You can copy the results to the top pane and execute. The only potential
issue is if you have a situation like this:
userA.tableFoo
dbo.tableFoo
Or
userA.tableFoo
userB.tableFoo
Because it will crap out when you try to force dbo to own two objects with
the same name...
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uTuvD0yzFHA.904@.tk2msftngp13.phx.gbl...
> This will generate the script for you for all user-defined views, tables,
> stored procedures and functions that aren't already owned by dbo:
> SELECT 'EXEC sp_changeobjectowner
> '''+TABLE_SCHEMA+'.'+TABLE_NAME+''',''dbo'''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),
> 'IsMsShipped')=0
> AND TABLE_SCHEMA != 'dbo'
> SELECT 'EXEC sp_changeobjectowner
> '''+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+''',''dbo'''
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMsShipped')=0
> AND ROUTINE_SCHEMA != 'dbo'
> You can copy the results to the top pane and execute. The only potential
> issue is if you have a situation like this:
> userA.tableFoo
> dbo.tableFoo
> Or
> userA.tableFoo
> userB.tableFoo
> Because it will crap out when you try to force dbo to own two objects with
> the same name...
>
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
of
and
>sql