PowerShell Script to add a Active Directory domain group to Sql Server syadmins

owmart
over 4 years ago

Does any one have any experience writing a script that would grant an AD domain group sysadmin rights to a SQL Server?

Parents
No Data
Reply
  • over 4 years ago

    Below are two scripts that use SMO to accomplish this task.

    If the login already exists, just use:

    $sqlserver = "sqlserver"
    $group = "ad\dbas"

    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver

    $sysadmin = $server.Roles["sysadmin"]
    $sysadmin.AddMember($group)

    To add a domain group to SQL Server, then grant it sysadmin access, use:

    $sqlserver = "sqlserver"
    $group = "ad\dbas"

    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver

    $login = New-Object Microsoft.SqlServer.Management.Smo.Login($sqlserver, $group)
    $login.LoginType = "WindowsGroup"
    $login.Create()

    $sysadmin = $server.Roles["sysadmin"]
    $sysadmin.AddMember($group)
Children
No Data