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

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

Building a Proper SQL Server Database Security Model

Free MSSQLTips Webinar: Building a Proper SQL Server Database Security Model

In this webinar we'll look at how to secure a SQL Server database for home-grown applications and for third-party database applications.


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?


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;

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:

 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.

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
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) + ';
'' 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

Last Updated: 2013-11-08

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand 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,, and also blogs at

View all my tips
Related Resources

Comments For This Article

Friday, July 14, 2017 - 10:51:51 AM - NewmcBes Back To Top (59347)
Thanks a lot.

Friday, November 22, 2013 - 2:41:48 PM - Aaron Bertrand Back To Top (27579)

Thanks Mark, you're absolutely right, I added that clause very late in the game and had validated the script right up until that addition. This line:


 AND default_schema_name &lt;&gt; ''dbo'';
Should read:
 AND default_schema_name &lt;&gt; N''dbo''
There will be a correction posted soon. 

Friday, November 22, 2013 - 1:04:18 PM - Mark Powell Back To Top (27577)
The final code appears to have a syntax error when ran on SQL Server 2008 R2 SP2. Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'AND'. which references line @innersql = @innersql + N''USE ' + QUOTENAME(name) + ';


Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Encrypting passwords for use with Python and SQL Server

SQL Server Permissions List for Read and Write Access for all Databases

get free sql tips
agree to terms