locked
How to use SQL default groups in User Rights Assignments RRS feed

  • Question

  • We use GPO's extensively to control our servers. With SQL, there are local groups that are server specific (i.e. SQLServerSQLAgentUser$COMPUTERNAME$MSSQLSERVER). Is there a way to set the URA to insert the computer name so we don't have to use a GPO per-server (we have thousands - way too much overhead)? We have always used a "baseline" policy and only apply server-level policies for very specific app owner needs.
    Friday, February 25, 2011 1:06 PM

Answers

  • Thanks guys,

    After further testing and review, maintaining another set of local groups is not manageable in our large environment. Instead, we leveraged the service accounts and locally set security principals (e.g. NT SERVICE\MSSQLSERVER) that are in the local SQL groups into the policy and that seems to work.

    Thanks for the suggestions!

     

    • Marked as answer by Jorree Wednesday, March 9, 2011 6:25 PM
    Wednesday, March 9, 2011 6:25 PM

All replies

  • If you add AD groups in SQL Server security, you add only the group and not the members accounts.
    Friday, February 25, 2011 3:33 PM
  • Thanks, but I'm not referring to or need to change anything in SQL security. This is strictly at the OS level User Rights Assignments.

    Ex

    Adjust memory quotas for a process: I need to SQLServerSQLAgentUser$COMPUTERNAME$MSSQLSERVER to ensure ALL SQL servers have the proper group defined, however, since the group name is server specific, I can't do that.

    Also, this is SQL Server 2008 R2.

    Friday, February 25, 2011 7:23 PM
  • You can create generic local groups such as LockPagesInMemory and specify these in the GPO. Add the service account to the appropriate user group for the permission needed. The creation of the group and adding the service account can be scripted fairly easily, especially with Powershell or a similar solution, so you could even have a process running from a scheduler checking and verifying.

     


    K. Brian Kelley, http://www.truthsolutions.com/
    • Marked as answer by Alex Feng (SQL) Monday, March 7, 2011 12:07 PM
    • Unmarked as answer by Jorree Wednesday, March 9, 2011 6:21 PM
    Saturday, February 26, 2011 12:08 AM
  • Thanks K,

    We used to do that with IIS when they had IUSR_machinename accounts and that worked great. Thank goodness they stopped doing that!

    My concern with doing it with SQL08R2 on WS08R2 is it might break the 'Managed Service Account' feature. I'm worried there will be areas where the feature won't work because I used other groups or accounts in the URA instead of what accounts/groups are installed and used by default.

    Monday, February 28, 2011 1:30 PM
  • You can put the generic group (you created) into those default groups.

    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Monday, February 28, 2011 3:22 PM
  • Thanks guys,

    After further testing and review, maintaining another set of local groups is not manageable in our large environment. Instead, we leveraged the service accounts and locally set security principals (e.g. NT SERVICE\MSSQLSERVER) that are in the local SQL groups into the policy and that seems to work.

    Thanks for the suggestions!

     

    • Marked as answer by Jorree Wednesday, March 9, 2011 6:25 PM
    Wednesday, March 9, 2011 6:25 PM
  • Hi,

    Yes and how to include SQLServerSQLAgentUser$COMPUTERNAME$MSSQLSERVER local group  ... into another local group ? which one is used for "Replace a process level token" and "Adjust memory quota for a process"

    It's impossible to manage local policies by GPO for a SQL Servers group ? NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT are unknown and impossible to add to a GPO to control local policies.

    Something yet well developed to be controlled and secured with GPO !!!!!

    Kind Regards

    Wednesday, December 14, 2011 11:25 AM