none
Which database Windows AD login use by default when he is a member of multiple windows groups

    Question

  • When a Windows AD user exists in multiple Windows groups, these groups have SQL Server logins and different default databases, how does SQL Server decide which database the domain user should use by default?

    For example, Windows domain account domain1\user1 is a member of domain1\group1 and domain1\group2. There are two Windows Authentication SQL login: domain1\group1 and domain1\group2. The default database of domain1\group1 is db1 and default of domain1\group2 is db2.

    Case 1: There is no SQL login for domain1\user1. When domain1\user1 logs in, which database is the default one, db1 or db2?

    Case 2: There is SQL login for domain\user1 and the default is db3. Which database is the default one, db1, db2 or db3?



    • Edited by Tingda Lu Wednesday, August 09, 2017 6:38 PM
    Wednesday, August 09, 2017 3:24 PM

All replies

  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c8e44063-b5af-4ff9-bf53-94c8bd016d29/sql-2008-users-in-multiple-ad-groups-what-is-default-database?forum=sqlgetstarted

    /*

    When a AD group member login  connects to SQL Server it looks for a deny access privileges , and if not found, then SQL Server picks the first grant access group that it finds and the user will receive the default database of that group login

    I have doubts SQL Server has an algorithm  to identify to which database user w*ill get into.....

    */



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 10, 2017 5:05 AM
  • Hi Tingda Lu,

    >> Case 2: There is SQL login for domain\user1 and the default is db3. Which database is the default one, db1, db2 or db3?

    Db3 will be the default database if SQL login for domain\user1 exists. SQL Server always receives the default database of AD user prior to default databases of Windows groups if it exists, despite it may be a member of multiple Windows groups.

    If you have any other questions, please let me know.

    Regards,

    Hannah

    Thursday, August 10, 2017 9:54 AM
  • Hi Tingda Lu,

    Any update on your issue?

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 22, 2017 6:10 AM