Sune Rievers blog

I recently had to task of changing all tables on a MS-SQL database to a new owner (well, dbo), and then I stumbled across this blog post, quoted here:

Normally you use sp_changeobjectowner(SQL Server 2000 SP3)

Sample:
Remenber to have the right permissions. In this sample I’m running as dbo and i have logged by using sa

use Northwind
EXECUTE sp_changeobjectowner 'EmployeeTerritory', 'guest'

To take back this, the parameters change a little:

use Northwind
EXECUTE sp_changeobjectowner 'guest.EmployeeTerritory', 'dbo'

Well you must do it for all tables, but using an undocumented Stored Procedure called sp_MSforeachtable you can do this in one line of code:


use Northwind
sp_MSforeachtable @command1="sp_changeobjectowner '?', 'dbo'"

Another undocumented Stored Procedure is sp_MSforeachdb which could be used for change the owner of all Databases

All views and stored procedures must however still be changed manually, but this is far easier, as they don’t contain unscriptable data (why oh why, MS-SQL, don’t you have a dump feature like MySQL has had for years??).

Grats and thanks to Jose Blanco Muradas for finding this