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.
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:
Run this guy…
Et voila! Let’s go check our work.
I hope this helps you to find potential pain points and proactively fix them.