locked
How to assign AD group default schema RRS feed

  • Question

  • Hi,

    We have AD group. Users are member of this group. I want to assing default_schema to this AD group. Why I right click user in db, selected properties, default schema is not selectable. How to to assing default schema to a windows AD group? (Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

    Thanks

    Friday, May 17, 2013 9:56 PM

Answers

  • Having a Default Schema for a Windows Group is a new feature in SQL Server 2012.  

    The behavior is documented here:

    If the user has a default schema, that default schema will used. If the user does not have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. If the user does not have a default schema, and is a member of more than one group that has a default schema, the schema of the Windows group with the lowest principle_id will be used. (It is not possible to explicitly select one of the available default schemas as the preferred schema.) 

    CREATE USER

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fanny Liu Tuesday, May 21, 2013 9:49 AM
    • Marked as answer by Fanny Liu Tuesday, May 28, 2013 10:09 AM
    Friday, May 17, 2013 10:52 PM
  • If you run the below -

    USE [db_name]
     GO
     ALTER USER [ADGroup_name] WITH DEFAULT_SCHEMA=[schema_name] 

    GO

    do you get the below error?

    The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15259)

    I actually did some more research and looks like you are not alone. This was even reported at Microsoft connect. Some links as below -

    http://connect.microsoft.com/SQLServer/feedback/details/328585/default-schema-for-windows-group#details

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

    Basically it turns out it's not allowed because if a user is member of two groups each of which is having different default schema - what should be the default schema of that user now? duh! Here's naother thread from our forum -

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/ddabd7b8-a8ad-40b5-ae66-e54b6f5af2a3/

    • Edited by Pradyumna Poddar Friday, May 17, 2013 10:32 PM Add more details
    • Proposed as answer by Pradyumna Poddar Friday, May 17, 2013 10:32 PM
    • Marked as answer by Fanny Liu Tuesday, May 28, 2013 10:09 AM
    Friday, May 17, 2013 10:13 PM

All replies

  • If you run the below -

    USE [db_name]
     GO
     ALTER USER [ADGroup_name] WITH DEFAULT_SCHEMA=[schema_name] 

    GO

    do you get the below error?

    The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15259)

    I actually did some more research and looks like you are not alone. This was even reported at Microsoft connect. Some links as below -

    http://connect.microsoft.com/SQLServer/feedback/details/328585/default-schema-for-windows-group#details

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

    Basically it turns out it's not allowed because if a user is member of two groups each of which is having different default schema - what should be the default schema of that user now? duh! Here's naother thread from our forum -

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/ddabd7b8-a8ad-40b5-ae66-e54b6f5af2a3/

    • Edited by Pradyumna Poddar Friday, May 17, 2013 10:32 PM Add more details
    • Proposed as answer by Pradyumna Poddar Friday, May 17, 2013 10:32 PM
    • Marked as answer by Fanny Liu Tuesday, May 28, 2013 10:09 AM
    Friday, May 17, 2013 10:13 PM
  • Having a Default Schema for a Windows Group is a new feature in SQL Server 2012.  

    The behavior is documented here:

    If the user has a default schema, that default schema will used. If the user does not have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. If the user does not have a default schema, and is a member of more than one group that has a default schema, the schema of the Windows group with the lowest principle_id will be used. (It is not possible to explicitly select one of the available default schemas as the preferred schema.) 

    CREATE USER

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fanny Liu Tuesday, May 21, 2013 9:49 AM
    • Marked as answer by Fanny Liu Tuesday, May 28, 2013 10:09 AM
    Friday, May 17, 2013 10:52 PM