Easier way to change every user in every database to default schema = dbo?

Question

There was recently a domain migration where our SQL Server is hosted.

I have two servers and about 30 DBs on each. Every user was changed to the new domain but in doing so, their default schema changed from to their domain when we want in defaulted to DBO.

I'm currently changing them manually by clicking on a DB -> Security -> Users then in the general properties tab, manually typing dbo in the default schema box.

Is there a way to write SQL code to run and adjust the default schemas for all of my users across all DBs?

Answer 1

So what you need to end up with is something like this, for all users in any database that don't already have a default schema of dbo:

USE db1;
ALTER USER user1 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user2 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user3 WITH DEFAULT_SCHEMA = dbo;
USE db2;
ALTER USER user4 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user5 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user6 WITH DEFAULT_SCHEMA = dbo;

Here is one way to do this, using aggregate concatenation and nested dynamic SQL, that gets something very close (the USE command is repeated for each user in any database, something you can probably resolve through more tweaking, but not sure it's necessary):

DECLARE @sql NVARCHAR(MAX) = N'', @innersql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT @innersql += ''USE ' + QUOTENAME(name) + ';
 ALTER USER '' + QUOTENAME(name) + '' WITH DEFAULT_SCHEMA = dbo;
 '' FROM ' + QUOTENAME(name) + '.sys.database_principals
 WHERE type_desc IN (N''SQL_USER'',N''WINDOWS_USER'') 
 AND default_schema_name <> ''dbo''
 AND name NOT IN (N''dbo'',N''guest'');'
FROM sys.databases WHERE state = 0;
EXEC sp_executesql @sql, N'@innersql NVARCHAR(MAX) OUTPUT', @innersql OUTPUT;
-- you can just print the command to preview it
-- (though it may be too long to review the whole thing):
PRINT @innersql;
-- once you're happy with it, uncomment this and run it all again:
-- EXEC sp_executesql @innersql;

• Kermit: You should make a blog post out of this.
• Aaron Bertrand: @Fresh ha, true
• Hannah Vernon: Must have more blog posts...