Quick way to transfer schema ownership of tables

For some reason my when I create new tables in my development environment all the tables have my username as the schema owner (instead of dbo).

So I came up with a quick script to reset all the tables to dbo.  I do this because it can cause problems if tables have different schema owners and someone doesn’t have access to see what’s in your schema (like another developer).

exec sp_MSforeachtable “ALTER SCHEMA dbo TRANSFER ?”

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s