Monthly Archives: January 2014

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 }
        { return "$DatabaseServer\$InstanceName" }

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

    $sqlConnection = $null


        $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]"
         $Command.ExecuteNonQuery() | Out-Null

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

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

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

Updated ImagePatcher.ps1

A quick post about an update I did a while ago.

There is a great patching script that is hosted on codeplex for patching wim / vhd. I have made some minor updates to it so it can also patch VHDx and will just run with windows 8 and require no additional components.

To patch a VHDX of 2012 R2 and only download what is needed
for that os.

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\WS12R2DG2.vhdx

To Patch only image 1 in the WIM

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\install.wim -patchimages 1

To patch Images 1 and 3 in the WIM

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\install.wim -patchimages "1,3"

To patch all images in a WIM

 .\imagepatcher.ps1 -ImageOnly $true -imagefile F:\VHD\install.wim -patchimages All 

So there is an offline VM servicing tool for OS level
Patches.  There are some for the 2008R2 / Windows 2012 that need to have
the image updated manually as they depend on other patches that have not been
installed yet. 

So best is to run this over your image then power it up and
sysprep it then shut it down and re-run it and all updates should be installed.

I would be expecting to get the following message for each
image / patch run.


It is because there are updates that are listed that wont
install due to patch requirements.  (Requires cluster J or DC role etc..)

Updated script can be found here.