Change the owner of all tables in SQL Server

(Cambiar el propietario de todas las tablas en SQL Server)

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

Of course among of this use you can manage to make more interesting things mainly if You are a DBA


Another tip: If you are into the Entreprise Manager seen the data in a table and you want to modify a field an insert null in it, put the cursor in it and press CTRL+0 (Control and zero).

Permalink: Change the owner of all tables in SQL Server