//
you're reading...
SQL Server

SQL Server: Change the Owner of All Tables to “dbo”

This is a simple piece of T-SQL code that allows you to change the ownership of all tables in a given database. In the example the ownership is being changed to “dbo” but that could be replaced with any owner andor schema you wanted.

It does use an undocumented stored procedure sp_MSforeachtable wich enumerates all the tables in the current database. There is also a sister stored procedure sp_MSforeachdb gives a DBA the ability to cycle through every database in your catalog. I find this one particulalry useful when running certain DBCC comands such as checkdb.

sp_MSforeachtable @command1=”EXEC sp_changeobjectowner ‘?’,’dbo’”

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

Comments are closed.

%d bloggers like this: