Default Schema for Windows Group in SQL Server


By:   |   Updated: 2015-10-19   |   Comments (2)   |   Related: More > Security

Problem

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

Solution

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:

ALTER USER [Domain\GroupName] WITH DEFAULT_SCHEMA=[dbo]

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.


Last Updated: 2015-10-19


get scripts

next tip button



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.

View all my tips
Related Resources




More SQL Server Solutions











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.





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

 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,

Ahmad

 


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

 Hi Ahmad,

This does not seem to work in SQL2016.

Any idea what i'm doing wrong?

 

Thanks!

 



download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

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





get free sql tips
agree to terms


Learn more about SQL Server tools