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