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.

6 comments

  1. Shouldn’t your script be skipping the system databases Master, Model, MSDB and TempDB?

    Also, shouldn’t it be skipping default schemas, where the schema name and schema owner are the same? For example, what is the benefit of changing each of the following to [dbo]:

    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_owner] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_accessadmin] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_securityadmin] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_backupoperator] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_datareader] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_denydatareader] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] to [dbo];

    Also, the first three DDL statements created for every database resulted in an error for me, when I tried running them on a few test (throw-away) databases:

    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[guest] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[INFORMATION_SCHEMA] to [dbo];
    USE [DBName]; ALTER AUTHORIZATION ON SCHEMA::[sys] to [dbo];

    Like

  2. Hi Tom,

    So, I should have definitely left out tempdb! I did purposely leave the system databases in because I know of too many times when people put user objects in the system databases.

    I had not run across the issue you are describing with the error. Thank you for letting me know! I will get that fixed.

    Liked by 1 person

  3. Hi Pamela,

    I made a change to eliminate tempdb. I also made a change to the ordering of temp table #t records, such that the three system databases would be listed first. That way, they are listed first in the output. Can you check out my work, and let me know if the changes I made are “okay”, or if there is a better (smarter) way of accomplishing these goals?

    My changes include adding AND database_id 2 and adding an expression to the ORDER BY clause:

    SET
    @dbCursor = CURSOR
    FOR SELECT name
    FROM sys.databases
    WHERE source_database_id IS NULL
    AND is_read_only = 0
    AND database_id 2 — Exclude tempdb
    AND state_desc = ‘ONLINE’
    order by (CAST(case
    when name in (‘master’,’model’,’msdb’,’tempdb’) then 1
    else 0
    end AS bit)) desc, name asc;
    — ORDER BY name;

    Also, I discovered a non-fatal error. The code runs to completion, but it was not listing one of my databases–a larger case sensitive version of the StackOverflow database. To fix this, I changed the FROM clause in this line of code:

    FROM information_schema.schemata
    to
    FROM INFORMATION_SCHEMA.SCHEMATA

    Like

  4. Thank you so much, Tom! I appreciate your work on this. I’ve been in the process of a move and getting ready for PASS Data Community Summit, so life over here has been a little hectic. You pitching in and helping as you did not only helped improve the code, but made my day. 🙂

    Like

Leave a 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 )

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