PowerShell to add LocalSystem to Sysadmin group in SQL 2012

Today I was working on a newly deployed Operations Manager system and there were a number of SQL servers that were not getting monitored due to default permissions that are implemented in the SQL management pack.

After a quick discussion with the client it was decided to add add the LocalSystem account back in to the Sysadmin group so it would work closer to the way that SQL 2008 / 2005 did.

Quick hunt around the internet and found some code that was posted David Brabant and thought that this looked like a good starting point.  In the case that I have the account exists and it just needs to be added in to the group.

function SQL-Get-Server-Instance
{
    param (
        [parameter(Mandatory = $true)][string] $DatabaseServer,
        [parameter(Mandatory = $true)][string] $InstanceName
    )

    if (!$InstanceName -or $InstanceName -eq "" -or $InstanceName -eq "MSSQLSERVER")
        { return $DatabaseServer }
    else
        { return "$DatabaseServer\$InstanceName" }
}


 function AddLocalSystemtoSysadmin
 {
     param (
         [parameter(Mandatory = $true)][string] $DatabaseServer,
         [parameter(Mandatory = $false)][string] $InstanceName = "MSSQLSERVER"
     )

    $sqlConnection = $null

    try
     {
         $Error.Clear()

        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName
         $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
         $sqlConnection.ConnectionString = "Server=$ServerInstance;Database=master;Trusted_Connection=True;"

        $Command = New-Object System.Data.SqlClient.SqlCommand
         $Command.CommandType = 1
         $Command.Connection = $sqlConnection
        $Command.CommandText = "ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]"
         $sqlConnection.Open()
         $Command.ExecuteNonQuery() | Out-Null
     }

    catch
     {
         $str = (([string] $Error).Split(':'))[1]
         Write-Error ($str.Replace('"', ''))
     }

    finally
     {
         if ($sqlConnection)
             { $sqlConnection.Close() }
     }
 }

$dbServers = @("db01","db02","db03")
foreach ($Computername in $dbServers){
     Write-host "Updating group on $Computername"
     AddLocalSystemtoSysadmin -DatabaseServer $Computername
} 

Leave a Reply