none
Granting Permissions to AD Group on SQL Server 2008 Instance

    Pertanyaan

  • 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
    03 Nopember 2010 15:20

Jawaban

  • 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

    03 Nopember 2010 16:00
  • 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.
    05 Nopember 2010 10:32
    Moderator

Semua Balasan

  • 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

    03 Nopember 2010 15:32
  • Sandesh,

     

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

     

    Thanks

    Pradeep


    Pradeep
    03 Nopember 2010 15:53
  • 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

    03 Nopember 2010 16:00
  • 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.
    05 Nopember 2010 10:32
    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.
    13 Nopember 2010 10:15
    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

    02 Februari 2011 17:10
  • 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

    • Disarankan sebagai Jawaban oleh Brad Gardner 24 Maret 2011 18:15
    03 Februari 2011 16:58
  • 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)

     

     

     

    18 Maret 2011 22:56
  • 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
    22 Juni 2011 19:15
  • Just thought I would chime in...  The UAC was the issue for me as well.

    19 Nopember 2011 1:29
  • It was the UAC for me also .. should've known .. thanks for pointing me in the right direction!
    23 Nopember 2011 18:30
  • 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!
    19 Juni 2013 23:36
  • 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

    12 Juli 2018 17:59
  • 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

    12 Juli 2018 21:57