Home Dashboard Directory Help
Search

Default Schema for Windows Group by Joseph Boschert


Status: 

Closed
 as Fixed Help for as Fixed


139
0
Sign in
to vote
Type: Bug
ID: 328585
Opened: 2/14/2008 8:32:10 AM
Access Restriction: Public
0
Workaround(s)
view
93
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
Posted by jfache on 8/16/2012 at 6:01 AM
hotfix this in SqlServer2008R2 for god's sake !!
Posted by AlvaroFernandez on 5/28/2012 at 4:00 AM
Hi,

I really second the backporting to SQL 2008 R2, perhaps in a major build/SP.

Recoding all apps to qualify schemas, unfortunately is not an option and while new apps are being developed, we need to handle app upgrades at customer sites.
Posted by Petra_fan3 on 10/6/2011 at 2:46 PM
I guess I should have looked closer before I posted. Looks like this is fixed in Denali. I've tested with CTP3 and you can now assign the default schema to a windows group. It would still be nice to go back and provide patches for 2005-2008 R2.
Posted by Petra_fan3 on 10/6/2011 at 2:33 PM
I'm relatively new to DBA and I was quite surprised to find this issue. It really just doesn't make sense. Please fix this in Denali (and updates for 2005/2008/2008 R2 would be nice as well.)
Posted by gdm_online on 8/24/2011 at 2:08 PM
Please provide a hotfix for this for SQL Server 2008 R2.
We have tons of code that will need to have "[dbo]." added to every table reference.
Posted by Nizamettin Özpolat on 6/24/2011 at 7:25 AM
Solution for 2008 must take place. Since Denali not released yet. And for my company the nearest upgrade date for deanli is 2013.
Posted by Mike Wade on 6/2/2011 at 7:28 AM
Are Microsoft providing a Hotfix for SQL 2008 R2 to fix this problem?    

In BOL it states "If DEFAULT_SCHEMA is left undefined, the user will have dbo as its default schema.". So as a windows group doesnt have a default_schema it surely there by implication must be dbo.    Thats the behaviour I need, thats whats documented, but its not what is implemented.

Having to wait and pay for upgrades hundreds of instances of SQL server to fix this doesnt seem acceptible when the documentend behaviour is not working.
Posted by Microsoft on 2/21/2011 at 8:34 AM
I'm happy to report that the next CTP for SQL Server Denali will allow default schemas to be assigned to Windows groups.

I'd like to thank everyone for their votes and attention to this issue. It made a different in being able to appropriately prioritize and make this improvement happen.

Il-Sung.
Posted by Philippe_R on 6/11/2010 at 2:28 AM
Really another very big breach in the security management.
I mean there's already one where you cannot prevent a user from using the DISABLE TRIGGER (you can only trace it) and now you cannot prevent the user from creating object on a non existent schema and there's no event in any trace for this creation! This is true also for SQL 2008.
Posted by allmhuran on 5/26/2010 at 5:18 PM
This is generally a problem for developers who need to create objects. With this in mind, could a solution be to have any group with dbo rights on a database map to the dbo schema by default? This resolves the problem with a user being mapped to two different AD groups and SQL therefore not being able to resolve the default schema: If either group has dbo privileges, the user is mapped to dbo. If not, they are mapped to their own schema (as per current behaviour).
Posted by TM-naiman on 5/6/2010 at 11:21 AM
Thank you
Posted by th_walther on 3/16/2010 at 5:53 AM
I run into this issue very often, too. In large enterprise environments the only possible way to manage sql database acces is with domain groups. But it's not possible to give a group a default schema. So you have to teach every user how to write full qualified tsql... And some applications will not run, so you have to fallback to single user rights instead of groups. Please make it possible to give groups a default schema.
Posted by Brian Boos on 3/15/2010 at 12:43 PM
I'm struggling with this issue as well. All access to domain SQL servers are controlled by Windows groups. If I want to maintain a sensible method managing permissions, I have no choice but to suffer through this issue.
Posted by Microsoft on 2/11/2010 at 10:02 AM
I'd like to thank everyone for their continued interest in this issue. As I mentioned in my previous post, we are investigating a solution for the next major release and gladly appreciate any feedback to this forum post, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/52cad95e-1382-4a7a-ad7c-56d9f99a6979.

Thanks,
Il-Sung.
Posted by Kaushik2903 on 1/28/2010 at 11:24 AM
what Happen to Microsoft ? busy with SQL 2008. but dont forget SQL2005. we need solution for this problem.

Thanks

Lazy DBA
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.
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 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 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 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 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 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 BobJ0101 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.
Sign in to post a workaround.