Database Object Schema and Windows Authentication using Windows Groups
-
Tuesday, November 13, 2012 10:43 PM
We use SQL 2008 R2 on Windows 2003 Servers, using Windows Authentication only. On our SQL Server, we've set up specific Windows GROUPS. Windows Domain Accounts are then added to the appropriate Windows Group. In SQL Server, we then create a SQL Login that correlates to the Windows Group, which in turn is granted appropriate access to a database. We don't create SQL Logins that correlate to a Windows User.
For example, Windows Group "_SQL_UserGroupA" may contain the domain user "DOMAIN\UserA". So in this example, we would create a SQL Login titled "_SQL_UserGroupA" (we do this by searching for the Windows Group during the SQL Login creation process), and assign this SQL Login the appropriate database rights (in this case, db_owner to a database named "databaseX").
Everything works well except that when a new table in the database is created, it is created using the Schema of the Windows USER assigned to the Windows Group, instead of the SQL LOGIN, even though that SQL Login (and subsequent Windows Group and Windows User) is a member of the db_owner database role.
For instance, if "DOMAIN\UserA" creates a table "tableXYZ" in database "databaseX", I would expect it to be created as "[databaseX].[dbo].[tableXYZ]". Instead, I am seeing it created as "[databaseX].[DOMAIN\UserA].[tableXYZ]". I don't understand this -- "DOMAIN\UserA" isn't even a valid user in the database. At worst, I could understand if the table was created as "[databaseX].[_SQL_UserGroupA].[tableXYZ]" where "_SQL_UserGroupA" is the SQL Login. This is creating havoc with some of our third party apps that are expecting to see the database tables with a specific owner. Yes, I can change the object owner to DBO and things work fine -- but I shouldn't have to do this after every time a new object is created.
What am I missing?? Thanks in advance.
All Replies
-
Tuesday, November 13, 2012 11:46 PM
It sounds like you are experiencing the cannot assign a default schema to a Windows Group problem which can be seen here...
http://connect.microsoft.com/SQLServer/feedback/details/328585/default-schema-for-windows-group
...and you can read more about it on this thread...
...If the code that creates objects does not exclicitly include the dbo.<tableName> in your case then unfortunately you will see this problem. I believe this has finally been fixed in SQL Server 2012.
Thanks
- Marked As Answer by Mark A. Olivet Wednesday, November 14, 2012 6:51 PM
-
Tuesday, November 13, 2012 11:58 PM
Yes, this is a well known pain point which has been addressed in SQL Server 2012, see Security Enhancements http://msdn.microsoft.com/en-us/library/cc645578.aspx. Sorry it is not available for you in SQL Server 2008.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
- Marked As Answer by Mark A. Olivet Wednesday, November 14, 2012 6:51 PM
-
Wednesday, November 14, 2012 6:53 PMExcellent info, thanks for the reply. I work for an agency in the DoD, and it took us nearly 4 years to get from SQL2000 to SQL2008R2. So...I would expect to have this resolved when we go to SQL2012...in 2018. lol
-
Sunday, November 18, 2012 12:51 PM
Yes, that is very unfortunate.
There is one method, that you could use, to manually prevent this from happening:
create a DDL Trigger that reacts on all "Object Creation" Statements and have it check the Schema of the object being created by comparing it to a manually maintained white-list (black lists are not accurate enough, unless you can be sure of the "domain-part", then it would be easier). The Trigger is synchronous, so you can then rollback the statement or do whatever with it.
it's some work, but if you face this Problem on many databases, many times, and for 4 more years, it actually saves some work.
cheers,
Andreas
Andreas Wolter | Microsoft Certified Master SQL Server Blog: http://www.vb-magazin.de/forums/blogs/andreaswolter/
.png)
