Feedback requested: Default schemas for Windows groups

Question

  

We want to seek your feedback on understanding the scenarios where you need to assign default schemas to Windows groups.  Based on the requests we have received and reading through one of the longest threads on this issue (http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/7d46a024-7ed5-4c9b-b091-3640dc04f5a1/), it seems there are multiple solutions that people are trying to achieve using default schemas:

-          Have a default schema as part of the user’s session that is set up by the application (through something like SET DEFAULT_SCHEMA = <foo>).  Any objects referenced or created will be bound to that schema.

-          Have a setting in SQL where it rejects any object reference without a schema.  This enforces the app to always use 2 part names (best practice).  This works best when you have access to the application source code or are writing a new application.

-          Have the ability for a DBA to specify a default schema on a Windows group so that legacy applications can continue to work and reference the correct objects, without requiring all the users to be provisioned.

 

Does your scenario fall in another bucket other than the ones listed above?  If so, can you please explain your scenario where this would be helpful? 

 

Secondly, if you really prefer the third case, how would you handle cases where a user could belong to multiple Windows groups? What schema should the user be associated with in this case when each group has a unique default schema?

Looking forward to your responses

Sameer Tejani
SQL Server Engine

---

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

Answer 1

Tough decision:

-          Have a setting in SQL where it rejects any object reference without a schema.  This enforces the app to always use 2 part names (best practice).  This works best when you have access to the application source code or are writing a new application.

Sure this would save all of ours lifes, but no one would ever switch that on for reasons of lazyness. Although this would be a great setting, I guess that after investigating the usage of that feature after several years, you would notice that most people stayed in the non-schema forcing mode and would you probably deprecate it. Sad thing !

-          Have the ability for a DBA to specify a default schema on a Windows group so that legacy applications can continue to work and reference the correct objects, without requiring all the users to be provisioned.

What if a user is a member of two Windows groups ? Would there be a limitation that only one Windows groups will have the option to have a default schema bound to it ?

-          Have a default schema as part of the user’s session that is set up by the application (through something like SET DEFAULT_SCHEMA = <foo>).  Any objects referenced or created will be bound to that schema.

Well, this is the best one of the options mentioned. A cool thing would be to bring back an information upon login request (informational type) that no schema could be determined for the connecting users and therefore he will be redirected to schema X unless the sepcifies the SET option.

Jens K. Suessmeyer

Answer 2

Instead of allowing groups to have their own schemas, one option might be to set a default schema for the whole database (so instead of dbo, it could be some other schema) and then any users created in the database could have their default schema set to a specific one or it could be set to *database default*. All groups would be set to use this *database default* rather than the dbo schema that they use at present.

Answer 3

If you have a Windows group that is not part of the db_owner role the schema that will be used is the one for the user. If no one exists a one will be created implicitly. To my best knowledge there is no way to stop this from happening. So if I have 20 developers that I do not want to add manually to the db server there is no way for me to stop these implicit creation of new schemas and local users in the database. I can deny explicit creation of new schemas but not these implicit ones.

This leads to many new local users and schemas created in the databases that should not be there. This is a major drawback and the reason that we have been allowing more permissions to our developers than necessary. I would really appreciate a solution to this.

I think that all the proposed solutions are acceptable. The option to require an explicit schema upon object creation I think is good and I cannot see that should be annoying to our developers. When you know the rule you will adapt to it rather quickly. If it is possible to enable/disable this requirement by the DBA it is even better.

Also the option to set a default schema on the Windows group sounds really good, then it is possible for all lazy programmers to disregard the schema and still get it right. :-)

Best regards
Lars

Answer 4

This problem also existed in SQL 2000.  Users who were not sysadmin, would create sqlagentjobs and DTS packages.  Those jobs and packages were owned by their respective accounts.  This prevented non-sysadmins from working with the jobs and packages.  A best practice for SQL 2000 was to make sure all jobs and packages were owned by "sa".  Similar to making every object owned by "dbo".  I think SQL 2005 "solved" this problem, by taking away the default ability to see a list of all objects... even if the user did not have connect permission to the object.

For the scenario where a user belongs to multiple Windows Groups, I think a conflict resolver is needed.  The conflict resolver would be an ordered list of schemas.  The user would be assigned to the schema, higher on the list.  If the DBA does not specify this ordered list, SQL Server should create one based on creation date.

Answer 5

Can you please explain wht SQL can't use the LOGINs mapped schema?

Each login has a User Mapping, that mapping allows the access to the database for normal users so why can't SQL Server create objects or reference Integrated Security connections using that schema?

(From my perspective, this does not necessarily have to fix a query that does not include a schema in the FROM clause).

Thanks

Answer 6

Can you please explain wht SQL can't use the LOGINs mapped schema?

Each login has a User Mapping, that mapping allows the access to the database for normal users so why can't SQL Server create objects or reference Integrated Security connections using that schema?

(From my perspective, this does not necessarily have to fix a query that does not include a schema in the FROM clause).

Thanks

In the scenario discussed here, the windows group login is mapped to a windows group user - and such users don't have a default schema. One of the requests in the past (option 3 in Sameer's post) was to allow assigning a default schema to such windows group users. Some of my comments on this problem are here.

Lars pointed out a related request related to default schemas - that is about preventing implicit user creation.

This thread should be marked as sticky, otherwise it will be lost. At the very least, my post will serve as a bump :)

---

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

Answer 7

The default schema is part of the Login properties of any Login on a SQL Server database, User Mapping.

The question is why doesn't the ODBC (or some other provider) use it when connecting to SQL using AD Groups. 

Is it SQL Servers fault or the provider?


I don't know who would want Domain\User schema creation as the default behaviour. 

So I say again, why can't the Logins' (for the AD Group) User Mapping defined default schema be used?

Answer 8

I understand the security concerns, so how about the following:  Keep the current default behavior, but allow default schemas for a group. But if a default schema is used, the groups members will not be able to create any objects.  The reason I need default schemas is for read-only access.

Answer 9

The default schema is part of the Login properties of any Login on a SQL Server database, User Mapping.

I'm not sure what you mean by that. Logins cannot have default schemas in general. A default schema is a database property, so only users can have default schemas - but not all users are allowed to have default schemas. See BOL on CREATE USER:

DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key.

---

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

Answer 10

  

-          Have a default schema as part of the user’s session that is set up by the application (through something like SET DEFAULT_SCHEMA = <foo>).  Any objects referenced or created will be bound to that schema.

-          Have the ability for a DBA to specify a default schema on a Windows group so that legacy applications can continue to work and reference the correct objects, without requiring all the users to be provisioned.

 

Resource Governor allows for us to specify resources based on classifier functions. Could something be built at the database level which would allow us to use classifier functions and set the default schema? Legacy apps where this may be important won't know to set the schema, even if such an option is provided. But if there is some way of classifying the application coming in and SQL Server enforcing a default schema, that might be helpful.

We tend to make heavy use of Windows groups and because of the way we do task-based security, it's not unusual for a Windows user to be a member of multiple Windows groups which ultimately map to users in the SQL Server database. Therefore, specifying a default schema on a Windows group might be problematic if there's a conflict. How would that resolve out? It sounds great in theory, but unless some sort of conflict resolution could be worked out, it may prove problematic.

---

K. Brian Kelley, http://www.truthsolutions.com/

Answer 11

Thanks for everyone’s feedback on this topic.  I have tried to address all the comments and concerns raised below.  It looks like there are two common requests coming out of this thread:

1. Default schema for Windows groups:
• There is a general demand for this feature – see the associated Connect bug (and votes) for this feature request: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328585

• How to resolve issues when a user is a member of multiple provisioned Windows groups with default schemas? This is the biggest issue we need to solve.  To solve this deterministically, our current idea (if a user does not have an explicit default schema) is to sort all the Windows groups based on their principal_id in sys.database_principals to determine the default_schema.  The first group in a user’s token that has a default schema based on this sort will be assigned to as the user’s default schema.  Furthermore, a lot of the feedback we have received through forums and conferences is that in a majority of the cases, there is only one group that is being specified as a user in the database which needs to be handled, so conflicting schemas don’t end up being an issue for them.  In the future we could enhance this by giving priorities to groups in order to manage conflicts better.

• Implicitly created schemas: There is a concern that without a default schema, implicit schemas are created for users (who connect by virtue of being a member of a Windows group).  If a user has a default schema, objects they create will end up in the default schema.  Therefore, with this solution, the user will be associated with the default schema and all the objects the user creates will be in the default schema (as opposed to getting an implicit one)
 
2. Default schema for DB
• To prevent someone from abusing the dbo schema, create a default schema property at the database level.  This is a new idea that we will look into further.

• It can be added on top of the current proposal of default schemas for windows groups as a catch-all to prevent users from creating implicit schemas or referencing the dbo schema.
 
For now, we would like to stick to a simple solution and go ahead and implement default schema for Windows groups based on the idea above.  Let us know if you have further feedback
 
Thx

Sameer
SQL Server Engine
 
 

 

Answer 12

For me, the idea of a default schema for a database is the best solution.  In my experience it is very rare that different groups accessing the same database need to use different schemas.  By far the biggest problem is to ensure all connections via Windows groups use the single approved schema for a given database.  Therefore this solution covers the functionality of applying a default schema to a group and has the following additional advantages:

a) In a situation where members of a group use two or more databases, it allows a different default schema to be set for each database.
b) If the database has a default schema defined, then when a user is added to the database no schema object should be created for the user name.  If the database has a default schema and we want a different schema to be applied to a new user then we should have to explicitly specify it.  This would eliminate the spamming of databases with schema objects that are almost always not needed.

Answer 13

I do find Windows Groups to be a very good way of granting rights and I use it almost exclusively.  I also define everyone’s default database and default language to be the same.  They can change these as they desire.

 

 

In the current implementation, if I have access to the server though a Windows group, but decide that I personally need a default database, it will materialize a login for me, just to keep track of that setting.  If I do not like it that someone has done that, then it is up to me (the DBA) to determine what I will do about it.  It offers an interesting insight, however, into options that could be pursued.

 

 

I apologize for not paying attention to this thread earlier, but here are my feelings, which do repeat some earlier comments:

 

 

1.   No default schema for Windows Group logins.  What does that even mean? 

I have people with access to a database through several different Windows Groups.  Some Windows groups have rights in several databases.  (Think of them as federated databases that make up a single logical data environment.)

 

Related Note:  I feel the same way about Windows groups having default databases and languages, namely "get rid of them" or at least "ignore them".  As it is, if a Windows login only gets access to the SQL Server through groups and those groups have different defaults, it is indeterminant (from anything I can intelligently recognize) which group "wins".  It can also change in unexpected ways as groups are added and deleted.  (I would also argue that default databases and default languages per login are artifacts that probably need replacement.)

 

 

2.    Create the default schema in the SQL Server.  There could be a hierarchy:
Level 1:  Server Default for schema 
   Level 2: Database Default for schema.  Database can inherit the server default.
      Level 3: User Default for schema. User can inherit the database default.

 

Define a (self) schema which could be used when it is actually desirable to have a clutter of uncontrolled schemas added to a database.

 

 

3.    As noted for Level 3 a user default in a database can be specifically assigned, even if the user gets access through a group.  This would materialize a user (e.g. name=domain\login) for maintaining that setting.

 

4.    It may be useful (although I think I would have little use for it) to consider an implementation for assigning a default schema to a Windows Group.  Namely: Use the Windows group as a macro of domain logins that need a setting.

Assigning a default to a Windows Group would materialize every member domain login as a user with the setting.  The Group remains a group and the setting is on the user.  Ideally the materialized user would track which group caused it to exist in its current state or setting.  That way it could be included or excluded when making further group based changes.

A GUI for this would have to deal with a few issues, of course.  A stored procedure for doing this would also need some behavior controlling parameters.

 

 

5.    In addition, I note that default database and language can be overridden when making a connection to a server.  If default schema could also be overridden at connection time, then that is another source of where the default might come from. 

 

FWIW, 
RLF

Answer 14

I seek to simplify my database security by giving access to Windows groups instead of specific users. All software developers have the same rights, so I should be able to give access to the Software Developers Windows group instead of to twenty individual Windows user accounts. These users only have access via one Windows group, so there is no potential collision of default schemas, yet you force me to create a user account for each one just so they can perform simple actions like saving the results of a Profiler trace to the database for analysis.

I propose that you either (a) allow a database-wide default schema, or (b) allow Windows groups to have default schemas and require a user account to resolve the collision of multiple default schemas ONLY if there ARE multiple default schemas for a particular Windows user.

Answer 15

My requirement is quite simply to:

1. assign the same permissions to users who perform the same job role, i.e. who are members of the same AD group. Assign those permissions once for the group, not individually for each user.

2. allow all users to use the same (dbo) schema by default, and especially ensure they don't create objects in any schema other than dbo.

Therefore I need to be able to assign dbo as the default schema for a user mapped to a login for an AD group. It is not realistic to disallow the creation of objects because of the scenario where we use an application service account (i.e. which is part of a group) to apply an upgrade to a vendor-sourced application, which requires the addition of new objects to the dbo schema of the database. At present I am forced to temporarily add a login and user for that service account, map it to use the dbo schema by default, apply the upgrade, then remove the user and login. This is cumbersome, prone to error and should be unnecessary.

Answer 16

I would be very happy to resolve this through connection strings.

Connection string method 1, applying only to schema selection:

Suggested connection string method 1 (I've seen it suggested already): Set a connection specific schema to override any default schema. This can logically be combined with all existing login specifications as far as I can tell.

Connection string method 2, making integrated security deterministic:

When your connection string specifies use of integrated security, it should accept a supplementary token that explicitly identifies the SQL-login you wish to be associated with. If both your user and two windows groups (that you qualify for) are SQL-logins, you can specify one of them in the connection string, and get all properties from that (security and defaults).

Of course, my first suggestion would still be compatible with my second suggestion.

I am currently developing an application that could make very good use of the explicit login with integrated security. It gets connection settings from registry (hkcu, hklm, hkcu/policy-key), and we already make frequent use of security filtered user policies. In my case organisation specific, prioritised and security filtered group policies would empower me to exercise manageable and highly flexible control over SQL connections.

Answer 17

I'd be happy simply to have a default schema in addition to the default filegroup for the db, with or without any kind of override for the role/group/user.

Answer 18

I like the idea behind Brian Kelley’s Post: Having a logical layer inside a database, implemented by a DBA .

Why do I stress that? The alternative, letting it decide by some ID is way too unpredictable in my eyes. What if group memberships change? Suddenly objects land in a different schema. Awful from documentation and reliability perspective, isn’t it?

I know, logic adds overhead. But it’s user-defined (a plus for me), and only allowed by a db_owner.

The other advantage is that we solve both problems with one technique:

We get rid of the automatically created schema, based on the Windows-User behind as well as the issue of multiple group-memberships.

 

A default schema in general is not bad at first sight. But actually it opens the next problem:

Who will have which permissions in that schema? – If the Login creates objects like a view, he could access data from other group-members who created tables inside that very same schema.

One would have to implement a new rule on object-ownership then: “objects within a schema belong to the schema owner – except in this special schema(??)”


Having considered that, I stay with the version of letting a DBA decide how to structure security and ownerships within his database - by giving him a tool for that.

Answer 19

I fall into the third category, except my "legacy" applications are vendor applications that I'm forced to maintain.  Regardless, I need to be able to assign a default schema to a user or group of users.

Since there is a very real issue in our environment where users can be in multiple groups, I suggest that if a default schema is specified for a group, a priority for that group is also defined within the database.  For instance, something like the following:

 

CREATE LOGIN [Domain\Group1] FROM WINDOWS WITH DEFAULT_DATABASE=[DB1]
CREATE LOGIN [Domain\Group2] FROM WINDOWS WITH DEFAULT_DATABASE=[DB1]
CREATE LOGIN [Domain\Group3] FROM WINDOWS WITH DEFAULT_DATABASE=[DB1]
CREATE LOGIN [Domain\Group4] FROM WINDOWS WITH DEFAULT_DATABASE=[DB1]
GO
USE [DB1]
GO
CREATE USER [Domain\Group1] FOR LOGIN [Domain\Group1] WITH DEFAULT_SCHEMA=[dbo] GROUP_PRIORITY 1
GO
CREATE USER [Domain\Group2] FOR LOGIN [Domain\Group2]
GO
CREATE USER [Domain\Group3] FOR LOGIN [Domain\Group3] WITH DEFAULT_SCHEMA=[HR] GROUP_PRIORITY 2
GO
CREATE USER [Domain\Group4] FOR LOGIN [Domain\Group4] WITH DEFAULT_SCHEMA=[Sales] GROUP_PRIORITY 3
GO

 

If a user is a member of all 4 groups, the user's default schema would be DBO (since Group1 has highest priority as specified by GROUP_PRIORITY).  If the user is only a member of Group 2, the user's default schema would be the same as their Domain Account, as it is now.  If a user is a member of Groups 2, 3, and 4, the user's default schema would be HR as Group3 has highest priority for groups that the user is a member of, and in Group2's case, not specifying a priority would be equivalent to that group having last priority. This priority-based evaluation will eliminate any conflicting default schemas of users in overlapping groups.  You could go so far as to specify special keywords like FIRST, LAST, etc (after GROUP_PRIORITY) when specifying priority level so you aren't required to know how many existing priority levels there already are in a database.  Yes this could easily turn into its own headache, but as it stands now, the only way to assign a "group" a default schema is to break said group down to specific users and assign each one a default schema, which (in my opinion) is much more of an administrative hassle than maintaining something like above.

Whatever the solution is, I think an overwhelming majority of SQL DBAs want this functionality, regardless of his/her reason.  Sooner rather than later would also be preferred.

Thanks,

John