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 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.
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:
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.
