Default schema for Windows group logins in SQL Server 2012

Alice is a tester in the Acme Corp. Alice has access to the testing database server through the Windows group login for the team [Acme\Testing]. When Alice creates a table without specifying a schema, her login name will be the default schema for the table as below:

[Acme\Alice].OrderDetail

When Bob from the same testing team tries to query the OrderDetail table by executing the following query, he will receive an error saying invalid object name. This is because he has specified the schema as ‘dbo’.

SELECT * FROM dbo.OrderDetail

A frustrated Bob reaches the DBA for help and they discussed the issue. After a few minutes of discussion, the DBA now understood the problem, the requirement, and fortunately the solution.

The problem is when a windows group user creates an object without specifying the schema, the windows login name is consider as the schema name (this is by design and exists there in SQL Server for quite some time). The requirement is to assign a default schema to a Windows group. And the good news is this is now possible in SQL Server 2012.

Using SSMS:

  1. Go to the database
  2. Expand Security and then Users node
  3. Open the Property page of the Windows user or group that you want to assign default schema

Default Schema for Win User in SQL Server 2012

In T-SQL you can use the below script

ALTER USER [Acme\Testing] WITH DEFAULT_SCHEMA=[dbo];

 

Advertisements

Leave a comment

Filed under Administration

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s