Tag Archives: PowerShell

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
} 

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.

image

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. ImagePatcher.zip

Enable remote access for Event Viewer via PowerShell

Today I had a situation where I wanted to connect using eventvwr to a remote machine.  I had been able to use PowerShell to connect to the remote machine but wanted the Event Viewer Gui for some quick filtering.

So opened up Event Viewer and entered my remote machine name and waited.  After about 30 seconds the following error came back.

image

Ok so by the looks of things we need to enable some firewall rules Smile

First thing is to get a list of the firewall rule groups that are on the remote server.

Get-NetFirewallRule | select displaygroup | Sort-Object displaygroup -Unique

image

Excellent they have not changed the group name.  Now we need to enable all the rules in that group.

Get-NetFirewallRule -DisplayGroup "Remote Event Log Management" | Enable-NetFirewallRule

Ok time to check if things have worked.

image

Perfect we now have remote access to the event log.