Property DefaultSchema is not available for Database [DatabaseName]

We're using Windows Authentication with our SQL Server. We've added a domain group to the SQL Server groups and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enable only for user entities. We cannot add a default schema to a group.
Is this by design, a bug or a problem with our SQL Server installation?

Updated on 2005/08/29

My first try was done at through Database\Security\Users. I tried going through Server\Security\Logins and I got this error message

Alter failed for User 'Domain\Group'. (Microsoft.SqlServer.SMO)
Additional Information:
--> An exception occured while executing a Transact-Sql statement or batch.
(microsoft.SqlServer.ConnectionInfo)

--> The DEFAULT_SCHEMA clause cannot be used with a Windows group or
with principals mapped to certificates or asymmetric keys.
(Microsoft SQL Server, Error: 15487)

Why can we not set a Default Schema to a Windows group? This would be more efficient then to add all the users to the Sql Server logins list.this is by design. Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then?|||I do understand the situation you described but in a well design group structure, a user will exist in only one group for a specific database. The group and schema structure will usually be matched. Otherwise it will become difficult to manage security efficiently. Therefore it should be possible to assign a Default Schema to a group.
The way it is now, if you have 100 users in a specific group instead of having to add only that group with a Default Schema, we now have to manage 100 different users within the database. If the default schema for these users have to change, there is a high probability that some will be forgotten.
I believe Microsoft has a good approach to security by using Domain\Group to which we give privileges and then we had the targeted users to the group. Why should this approach stop at SQL Server?|||

Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one.

Thanks
Laurentiu

|||Regarding this issue, I have a situation:

We have a group of developers that we would want to configure as db_owners of database "B". All this developers belong to network group "A". We associated this group to db_owner role in database "B" and thought it would be enough to configure the desired permission in the db.
The problem is when some developer (belong to group "A") tries to create a table in the db "B", get's the error: "Property DefaultSchema is not available for Database 'B'. This property may not exist for this object, or may not be retrivable due to insufficient rights. (SQLEditors)".

We tried then to configure the DefaultSchema for the group, but are unable to, because of the behaviour you refered earlier.

So, in this situation, how can I configure a specific network group to be db_owner (or have some other role)? Is it possible in SQL Server 2005? Don't tell me we have to configure all users, one by one...
In SQL Server 7/2000 we have no problem with this.

Tks.
|||

I found the root cause of the problem you described and it seems to be a bug in SQL Server Management Studio when trying to create a schema-bound object on SQL Server 2005. The tool is internally trying to find (without success) the default schema for the user and failing.

What should happen (to have the same behavior found in SQL Server 2000) is that it should create an implicit user and schema for the caller (let’s call it user1 for simplicity) and the table in the specified schema (notice that the default schema name is the same as user name). It is important to observe that at the end you end up creating an implicit user and a schema for every member of the group that creates an object in the database.

In order to get the same results right now, you have two possible options:

1) Create the first object using TSQL directly:

a. Click on “New Query” and connect to the right server

b. Write the script to create the table and click on “Execute”, for example:

use [db_Test]

go

CREATE TABLE [MyTable]( data varchar(10) )

go

c. If you refresh the view in Object explorer you will notice the following changes:

i. Table created = [user1].[MyTable]

ii. New user = [Domain\user1]

iii. New schema = [Domain\user1]

NOTE: At this point you can drop this “dummy” table and everything should work fine from now on for this particular user.

2) Explicitly create a user for [Domain\user1], Schema [Domain\user1] is completely optional

IMPORTANT NOTE: To have an equivalent behavior, you will need to revoke the CONNECT permission on the database to [Domain\user1] (REVOKE CONNECT TO [Domain\user1]), this way the only access to the database would be via the Windows group membership and not by an explicit permission.

I hope we were able to help you, let us know if you have further questions. We really appreciate your feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Is MS going to release a fix for this so we can assign a default schema to a group?

|||

Hi,

I find this limitation a real pain in the ***.

This should be the same if you couldn't set a default db any more for a windows group. Because you have the same problem in this case if a user is a member of 2 different windows group and those both groups do have a login into SQL with 2 different default databases.

I know that you're asking for trouble at that moment. But I know that this is a situation to avoid.

The common sense need to be followed with the default schema on windows groups. You know the consequences if you don't make proper use of it.

I really hope that whit SP1 we can set a default schema to a windows group.

Greetings

Ludo

|||

I'm glad to see that my view on this issue is not unique. I previously file a request to include this possibility in a futur release and it was also turned down.

I believe the need is real and Microsoft should come up with a solution. I understand their point but also that it coul be overcome by putting in the proper validation and dialog boxes scenario. The people at Microsoft were able to upgrade Sql Server from "acceptable" to "great", but I believe that there is still place for improvements and the possibility to assign a default schema to windows group is one of them.

Keep adding your comments and we might be able to turn this around.

Thanks!

|||

This issue is already on our radar. It is actively tracked; we are not ignoring it. For your reference, the item that tracks this is 299032.

Thanks
Laurentiu

|||

Thanks Laurentiu, this is good news.

Gilles

|||

I glad to hear this.

This is very good news.

I hop it will be afvailable in SP1 or with a seperated hotfix.

Greetings

Ludo

|||

I agree this is a must add feature. We have a group of developers all developing the same database. We want to grant the AD group that they all belong to all necessary permissions and set their default schema to dbo. As it stands, we cannot set the default schema and have to create individual accounts for all of them.

***This negates the whole convenience of granting granular db permissions to the AD group.***

Please fix this as it is very frustrating.

|||Indeed!!|||Can you please provide a link to the MS item number that you reference above?sql