none
Granting Permissions to AD Group on SQL Server 2008 Instance

    Question

  • Tech Folks,

        Good Morning!!!! Here is my question ....I am using "Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) ".

        I was asked to provide Sysadmin permssions on this instance to all Domain Admins. To provide requested access I have created a login for AD Group Domain Admins in SQL Server 2008. But members of this group can't access SQL Sever. If I add these members individually in SQL Server logins then only they are getting permissions. I tried to create login using both GUI and T-SQL, in both ways it is not working.

    Operating System: Windows Standard Edition SP1 64-bit

    Please reply if any one of you resolved it.

    Have a great day

     

    Thanks

    Pradeep

     


    Pradeep
    Wednesday, November 3, 2010 3:20 PM

Answers

  • Okay... If you give sysadmin permission, it should work. can you check if the AD Group is disabled? 
    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Wednesday, November 3, 2010 4:00 PM
  • Hi,

    Sandesh is correct. It works fine based on my tests - I create a test domain account, add it to the Domain Admins group; add Domain Admins as a SQL login and grant sysadmin permission and I can log on the SQL Serve use test domain account.

    For this issue, could you please post the login failed error message?

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, November 5, 2010 10:32 AM
    Moderator

All replies

  • Once you have created the login, Go to the properties of the login and then go to "Server Roles" tab and give sysadmin access there and click ok.
    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Wednesday, November 3, 2010 3:32 PM
  • Sandesh,

     

       Thanks for your Reply.I gave sysadmin permissions also. but it did n't work.

     

    Thanks

    Pradeep


    Pradeep
    Wednesday, November 3, 2010 3:53 PM
  • Okay... If you give sysadmin permission, it should work. can you check if the AD Group is disabled? 
    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Wednesday, November 3, 2010 4:00 PM
  • Hi,

    Sandesh is correct. It works fine based on my tests - I create a test domain account, add it to the Domain Admins group; add Domain Admins as a SQL login and grant sysadmin permission and I can log on the SQL Serve use test domain account.

    For this issue, could you please post the login failed error message?

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, November 5, 2010 10:32 AM
    Moderator
  • Any progress?
    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by cjmorgant1101 Wednesday, June 22, 2011 7:16 PM
    • Unproposed as answer by cjmorgant1101 Wednesday, June 22, 2011 7:16 PM
    Saturday, November 13, 2010 10:15 AM
    Moderator
  • I'm having the same exact problem. Standart SQL 2008R2 install, in a Domain. I have added Domain Admins as a login SQL login, I have given the it the SysAdmin under Server Roles and, unless I expressly add each administrator's own AD account, they cannot log in.

    Any clues?

     

    Thanks

    Wednesday, February 2, 2011 5:10 PM
  • I am experiencing the same issue as well. The Domain Admin Group is not disabled, and it is set as a sysadmin in the SQL. It only works for users if they are added individually

     

    thanks

    • Proposed as answer by Brad Gardner Thursday, March 24, 2011 6:15 PM
    Thursday, February 3, 2011 4:58 PM
  • The correct settings on a SQL 2008 server running on Windows 2008 to give admin access to the Domain Admins group are:

    - Add Domain Admins group in the SQL Server Security Login

    - Give Sysadmin rights to the Domain Admins group

    AND

    - Disable UAC or start the SQL Server Management Studio as an Administrator (Right-click on the icon)

     

     

     

    Friday, March 18, 2011 10:56 PM
  • The correct settings on a SQL 2008 server running on Windows 2008 to give admin access to the Domain Admins group are:

    - Add Domain Admins group in the SQL Server Security Login

    - Give Sysadmin rights to the Domain Admins group

    AND

    - Disable UAC or start the SQL Server Management Studio as an Administrator (Right-click on the icon)

     

     

     


    This was it for me...the UAC.  Accessed it with the "Run As Administrator" and was able to connect and do what I needed...also turned off UAC on the server and rebooted and able to access without the Run As...

     


    C.J. Morgan
    Wednesday, June 22, 2011 7:15 PM
  • Just thought I would chime in...  The UAC was the issue for me as well.

    Saturday, November 19, 2011 1:29 AM
  • It was the UAC for me also .. should've known .. thanks for pointing me in the right direction!
    Wednesday, November 23, 2011 6:30 PM
  • AND

    - Disable UAC or start the SQL Server Management Studio as an Administrator (Right-click on the icon)

     

    Just wanted to chime in also that the UAC tip was the one that mattered! -Thanks!
    Wednesday, June 19, 2013 11:36 PM
  • This REALLY should be documented somewhere. (Members of an AD group accessing SQL Server using SSMS must do so with elevated privileges and with UAC off when the only reason they have rights is that they belong to that AD group, which has a login to SQL Server.) That was the answer for me as well. Now the lingering question becomes: Why?? No other login that I know of needs that. Anyone knows?? TIA, Raphael

    rferreira

    Thursday, July 12, 2018 5:59 PM
  • The answer should be obvious: without UAC, Windows will not add the token for the AD group to their login token. Why not I cannot say for sure, since I don't know how this AD group was set up - but maybe it was set up as Administrator in Windows as well? In any case, that seems like a Windows question.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, July 12, 2018 9:57 PM