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:
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.
- Go to the database
- Expand Security and then Users node
- Open the Property page of the Windows user or group that you want to assign default schema
In T-SQL you can use the below script
ALTER USER [Acme\Testing] WITH DEFAULT_SCHEMA=[dbo];