Default Schema for Windows Group in SQL Server

By:   |   Comments (3)   |   Related: > Security


Is it possible to define a default schema for a Windows Group in SQL Server 2012?


In SQL Server 2008 R2 and earlier, you are not able to map the default schema for a Windows Authentication Group. You get the below error when you try:

The DEFAULT_SCHEMA clause cannot be used with a Windows group

The main security problem will be clear if you try to create a database object such as a table inside your database, a schema with the group member name will be created automatically and the created table will be within this schema.

For example, [MSFT\John] a member of the [MSFT\LANAdmins] Windows group, logs in and tries to create a new table named Services. A new schema will be created in the database named [MSFT\John] and the created table will be named [MSFT\JOHN].[Services]. If any other member within this group tries to select from this table, an Invalid Object Name error will be shown.

This security issue is resolved in SQL Server 2012, making it possible to assign a default schema for Windows Groups.

Set the Default Schema for a Windows Group

In order to set the default schema for a Windows Group, open SQL Server Management Studio, navigate to Security > Logins, right click on the Windows Group that you want to change and choose Properties. The below window will then open.

First you need to map the login to the database by checking the Map column, then click on the Default Schema column to select the schema. In our example, we selected dbo as the default.

Configure the default Schema for a Windows Group in SQL Server Management Studio

This can also be done using the T-SQL statement below:


Identify Default Schema

In order to identify the default schema for all database users, query the sys.database_principals system table:

SELECT name, type_desc, default_schema_name
FROM sys.database_principals
WHERE type in ('S', 'U', 'G');

The result will be something like:

Query to identify the default schema for all SQL Server database users
Next Steps
  • As you setup your Windows Groups in SQL Server be sure to define a default schema.
  • Consider checking your Windows Groups default schema as a step when you upgrade SQL Server.
  • Check out the SQL Security tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

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

View all my tips

Comments For This Article

Wednesday, May 19, 2021 - 1:01:57 PM - Javier Back To Top (88712)
Gracias, el scritp me sirvió mucho.

Sunday, September 17, 2017 - 3:08:06 AM - Ahmad Yaseen Back To Top (66360)

 Hi Dani,


Thank you for your input here.


Please note that i have tried it in a new SQL Server 2016 instance with the steps specified in this article and it is working fine.


Please share the error if you can.


Best Regards,



Friday, September 15, 2017 - 10:39:00 AM - Dani Back To Top (66316)

 Hi Ahmad,

This does not seem to work in SQL2016.

Any idea what i'm doing wrong?




get free sql tips
agree to terms