Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2013-11-08   |   Comments (3)   |   Related: More > 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


Last Updated: 2013-11-08


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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. 
Thanks!
Aaron

Friday, November 22, 2013 - 1:04:18 PM - Mark Powell Back To Top
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) + ';

Learn more about SQL Server tools