Search
Active

63
Sign in to vote
0
Sign in to vote
Sign in
 to vote
Type: Bug
ID: 328585
Opened: 2/14/2008 8:32:10 AM
Access Restriction: Public
0
Workaround(s)
60
User(s) can reproduce this bug
When you connect to SQL using Windows group, you cannot define a default schema. It is greyed out. This means that if you want everyone to be in a specific schema, you can't do it by default. When users connect through membership in a Windows group, SQL will automatically create a separate user (mapped to the AD acct), and a schema of the same name. So say you belong to the 'DBA' group in AD, and that group has rights in testDB. When you connect for the first time, SQL will create a user in testDB called domain\username and map it's default schema to domain\username as well. This causes you to have tons of user accts in your DB that you have no use for because you're supposed to be connecting through your group 'DBA'.

The best practice from Microsoft told us to use Windows security, and this issue has lasted for almost two years without an easy fix.

If you told us to do things in certain way, you need to gave us tools to do it. The BUG is very disappointing for DBA try to implementing the best practice.

An extensive forum has discussed this issue at length and is located here: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=159533&SiteID=17
Details (expand)
Product Language
English

Version

SQL Server 2005 SP2 - Standard Edition

Category

Tools (SSMS, Agent, Profiler, etc.)

Operating System

Win2003 Standard Server (SP2)
Operating System Language
US English
Steps to Reproduce
1. Create a group in AD
2. Place a user in that group
3. Have the user create a view through SSMS
Actual Results
An error with the following appears: "Property DefaultSchema is not available for Database [n]. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors).
Expected Results
If the schema doesn't exist it should default to dbo.
Platform
32
File Attachments
0 attachments
Sign in to post a comment.
Posted by Bob Johnson on 2/14/2008 at 9:56 AM
SQL has long resolved similar issues for logins that are Windows groups. For instance, if a Windows user is a member of two Windows groups that are in turn created as logins on a SQL Server machine, SQL is able to resolve possible differences in default database, language, etc. So it is beyond any possible justification that Microsoft has yet to in some way resolve this for default schema. Defaulting to the dbo schema in such a situation would certainly be an acceptable solution so long as the user is not automatically granted alter on the dbo schema.

At present, if a user that does not have a default schema by virtue of accessing via a group creates an object without qualifying it (ie: "create table" rather than "create dbo.table") will automatically cause a new schema to be created named after themselves. This user is then granted full ownership of this new schema. This happens even when the group login/user that they used to get to the database is EXPLICITLY DENIED create schema rights. This is totally unacceptable. A user should not be able to create a schema when they are denied that permission. Microsoft has tried to claim that the user is not actually creating the schema, that SQL Server actually is. While this may technically be true, it in fact always it technically true. Any user that creates a schema does so by submitting a request to SQL Server to create it, and SQL Server then creates is. In either case, the user caused SQL Server to create it so the attempt at justifying this by saying that the user didn't violate the "deny create schema" fails the test of basic logic. This is a bug, and this must be fixed. I would expect Microsoft to admit this and fix it. We are now in 2008, with SQL 2008 right around the corner yet this has been broken since SQL 2005. That's a long, long time. Too long.
Posted by Laurentiu Cristofor on 2/14/2008 at 4:58 PM
This should be treated as a request for the database engine to provide a way to associate a default schema to a Windows group. If such capability is added, the tools should be updated to make use of such a default schema.

This is not a tools issue, but a database engine issue with impact on tools.

This is a design change request, not a bug. It should be used as the primary way of tracking this request for the database engine and later for tools, if necessary - DO NOT CLOSE AS DUPLICATE even if internal items exist that already track this - close those instead and track the issue through this item instead.

For history and comments, check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1.
Posted by Ludo from Belgacom on 2/15/2008 at 1:09 AM
Microsoft promotes to use windows authentication. To make this easy to manage Windows groups are a great solution. Microsoft just refuses, us Dba's to use this if we want to make use of Schema's. The fact that a user can be member of multiple groups with each group having a different default schema and that it's impossible for SQL to decide what schema to use is given as excuse for it. What about default Database, each group can have a different default database as well... I think that in this case default schema for a group should be made possible and it's the responsibility of the DBa to manage it and to make it usable.
Posted by Adam Bean on 2/15/2008 at 12:54 PM
http://forums.microsoft.com/msdn/showpost.aspx?postid=79418&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0
Posted by Microsoft on 3/4/2008 at 7:59 PM
Thank you for taking the time to report this issue. We understand that this is an issue for you and other customers will investigate a solution, such as default schemas for groups, for a future release of SQL Server.

Il-Sung Lee.
Posted by jon.vickers on 8/21/2008 at 2:13 PM
The sysadmin server role handles this perfectly, so what ever you are doing there, copy and paste that code for the groups.
Posted by Stephen Mills on 7/7/2009 at 3:56 PM
I can't believe they ignore this. It has been an issue since the day SQL 2005 came out. Now SQL 2008 is here and still has the issue and SQL 2008 R2 is about to enter into CTP. I bet the same issue will be there as well, because otherwise they would have updated this ticket to show that it was resolved in a future version.
Posted by Microsoft on 11/2/2009 at 2:38 PM
Hi,

I want to assure everyone that we are not ignoring this and that we are investigating potential solutions. One thing that you can do to help is to post a response to this forum question and let us know what would be preferrable to you:

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/52cad95e-1382-4a7a-ad7c-56d9f99a6979

Thanks,
Il-Sung.
© 2009 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement