Default Schema for Windows Group in SQL Server

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.

Related Posts

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *