Discover and Clean Up Schema Ownership

My colleagues and I take schema ownership seriously.  The owner (with few exceptions) should always be “dbo”.  Certainly, it should not be a user.  Why?  Because if the user leaves and their account is disabled or deleted, we have a problem.  If you’re a DBA, you have enough problems without adding this one to your list. 

So, how do you find these offenders, much less fix them? Let’s create a test user, called TestUser, and make it the owner of a schema called FindThis in AdventureWorks2012.

Don’t be like me.

Now, let’s play hide-and-seek.  This script will find any schema that isn’t owned by dbo and give you a script to fix it. It won’t execute it for you in case some of these are intentional.  Take the scripts you need and run them in a separate window.

--Courtesy of Pam Mooney, https://thenonclutteredindex.com/

CREATE TABLE #t
    (
      DatabaseName sysname,
      schema_name sysname,
      schema_owner sysname
    );

DECLARE @dbName sysname;
DECLARE @dbCursor CURSOR;
DECLARE @sql NVARCHAR(MAX);
DECLARE @SchemaOwner sysname = 'dbo'

SET 
@dbCursor = CURSOR 
FOR SELECT name
	FROM sys.databases
	WHERE source_database_id IS NULL 
		AND is_read_only = 0
		AND state_desc = 'ONLINE'
		
	ORDER BY name;

OPEN @dbCursor;

FETCH NEXT FROM @dbCursor INTO @dbName;

WHILE ( @@FETCH_STATUS = 0 )
    BEGIN	
        SET @sql = 'USE [' + @dbName + ']
	INSERT INTO #t
	
	SELECT ''' + @dbName + ''' AS DatabaseName,
	schema_name, 
	schema_owner
	FROM information_schema.schemata
	WHERE schema_owner NOT LIKE ''' + @SchemaOwner + N'%''';
        EXECUTE sp_executesql @sql;
        FETCH NEXT FROM @dbCursor INTO @dbName;
    END;
	
CLOSE @dbCursor;
DEALLOCATE @dbCursor;

SELECT
    t.DatabaseName,
    t.schema_name,
    t.schema_owner,
    'USE [' + ( SELECT
                    t.DatabaseName ) + ']; ALTER AUTHORIZATION ON SCHEMA::[' + t.schema_name + '] to [dbo];' AS Script
FROM
    #t t;

REVERT;

DROP TABLE #t;

Here’s a copy of the output:

Found you!

Run this guy…

Et voila! Let’s go check our work.

To quote the song,
“Now that’s the way I always heard it should be…”

I hope this helps you to find potential pain points and proactively fix them.

One comment

Leave a Reply to Cleaning Up Schema Ownership – Curated SQL Cancel reply

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