Script to Set the SQL Server Database Default Schema For All Users

By: Aaron Bertrand   |   Updated: 2013-11-08   |   Comments (3)   |   Related: > Security

Problem

You may come across or even inherit a system where many of the users have different default schemas. This can cause multiple problems, including being unable to access certain objects, potentially accessing the wrong objects, and two users can even generate multiple copies of the same plan for the same query. Usually, this is easy to remedy - you go into Management Studio, right-click the user, and change the default schema through the UI. Or you write an ALTER USER command manually. But what if you have hundreds of users, across all of your user databases, that should all have the same default schema?

Solution

While it may sound weird, this is actually one of my favorite types of problems to solve. Even though I'm sure some folks out there would immediately think, "To the PowerShell!", while others will think they have a long night ahead of them pointing and clicking through the UI.

First, let's understand where we need to get. Assume we have a system with a large number of users who should all have dbo as the default schema, and it is unknown how many of them currently have a different default schema. The code we would need to use to switch them all to the same default schema would be:

USE your_database;
GO
ALTER USER [User1] WITH DEFAULT_SCHEMA = dbo;
ALTER USER [User2] WITH DEFAULT_SCHEMA = dbo;
ALTER USER [User3] WITH DEFAULT_SCHEMA = dbo;
...

Now, to find all the users in this database that have a *different* default schema, we can start with this query:

SELECT name 
 FROM sys.database_principals
 WHERE type_desc = N'SQL_USER' -- keeping it simple - no roles or windows logins/groups
  AND default_schema_name <> N'dbo' -- only users not already with this default
  AND name NOT IN (N'dbo', N'guest'); -- we don't want to affect these users

Keep in mind that you may have some users in some databases where the default schema is not dbo, and this is intentional. If this is the case, you can make minor changes to this initial query, for example change <> N'dbo' to NOT IN (N'dbo', N'other_schema').

Next, we need to build the ALTER USER command. We can do that inline, like this:

SELECT N'ALTER USER ' + QUOTENAME(name) + ' WITH DEFAULT_SCHEMA = dbo;'
 FROM sys.database_principals
 WHERE type_desc = N'SQL_USER'
  AND default_schema_name <> N'dbo'
  AND name NOT IN (N'dbo', N'guest');

This will print out a series of commands for you, but there are two inefficiencies here: (1) you have to copy the output and execute it manually, and (2) you have to repeat the process for every database. Let's deal with (1) first. Instead of just selecting multiple rows with a command in each row, we can build a string dynamically and print it or execute it.

DECLARE @cmd NVARCHAR(MAX); 
SET @cmd = N'';
SELECT @cmd = @cmd + N'ALTER USER ' + QUOTENAME(name) + ' WITH DEFAULT_SCHEMA = dbo;'
 FROM sys.database_principals
 WHERE type_desc = N'SQL_USER'
  AND default_schema_name <> N'dbo'
  AND name NOT IN (N'dbo', N'guest');
  
PRINT @cmd;
--EXEC sp_executesql @cmd;

This is a little better. I like to print out the command before I execute it, mostly for sanity checking. Important to note that if your list of users is large, the output will be limited to 8000 bytes, so you may not see the entire command (and it may appear to be truncated in the middle of a command). I can assure you that the entire command is there, it's just not going to appear that way due to the limitations of PRINT.

But we still haven't dealt with the fact that we want to affect users across *all* user databases. This is where it gets interesting. We need to build up a set of commands in each database, using dynamic SQL, and this will require an additional layer of dynamic SQL to build up those commands, once per database. I won't make you suffer through my trial and error, but here is what I ended up with:

-- @outersql will build a command for each database
-- @innersql will be the db-specific commands for each user
DECLARE @outersql NVARCHAR(MAX), @innersql NVARCHAR(MAX);
SELECT @outersql = N'', @innersql = N'';
-- here we build a USE command and all of the ALTER USER commands for each database
SELECT @outersql = @outersql + N'SELECT 
  @innersql = @innersql + N''USE ' + QUOTENAME(name) + ';
ALTER USER '' + QUOTENAME(name) + '' WITH DEFAULT_SCHEMA = dbo;
'' FROM ' + QUOTENAME(name) + '.sys.database_principals
  WHERE type_desc = N''SQL_USER''
  AND default_schema_name <> N''dbo''
  AND name NOT IN (N''dbo'',N''guest'');'
FROM sys.databases WHERE database_id > 4 -- non-system databases
AND state = 0; -- online
-- we need to execute the outer SQL to pull out the inner SQL
EXEC sp_executesql @outersql, 
  N'@innersql NVARCHAR(MAX) OUTPUT', 
  @innersql OUTPUT;
-- now we can print it to see the (perhaps partial) output
PRINT @innersql;
-- EXEC sp_executesql @innersql;

And also, if the list is quite large, you could take the set of commands that are printed, run those independently, and run the script again (those users should no longer show up). This will allow you to inspect the commands you're running before you run them.

Next Steps

• Identify whether you have users with the wrong default schema, and fix them with this script.
• Review these other tips and resources:
  • Script to move all objects to a new schema for SQL Server
  • Breaking ownership chaining within a schema in SQL Server
  • SQL Server Security Tips
  • User-Schema Separation (MSDN)

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips