Error: Executing DDL statment thru SSMS by a Windows Group user

Post SQL 2005 i had many calls from developers that they are not able to create a table even though they are a part of db_owner group and it error out with the below error,

 

"Property DefaultSchema is not available for Database '<dbname>'. This property may not exist for this object, or may not be retrivabledue to insufficient rights. (SQLEditors)"

This problem only occurs when user tries to create the table through GUI, This is by design in SQL server after the introduction of the concept 'SCHEMA' and happen only when the user is accessing the DB through a Windows group which is not part of SysAdmin, Also  SQL Server does not allow to SET a default schema for a Windows group user.

 

Let us imagine AD user DOMAIN\user1 is a part of AD group DOMAIN\group1 and AD group DOMAIN\group2 and both have access to a database1.

When the user connects to the database SQL server cannot choose a schema if the schema for the 2 groups are different. This is the reason why SQL server does not allow to SET a default Schema to a Windows Group user.

 

Workaround:

 

There are 2 ways you can overcome this problem, the simplest was is use TSQL Query with fully qualified name to create the table

 

            Eg:  CREATE TABLE [dbname].[dbo].[Testtbl]( col1 int )

 

Another way you can overcome this issue is to create a explicit user DOMAIN\user1 on the database and assign a default schema which will override the group user but you might need to create user for each login in the group which is not advisable.