Setting SQL permissions with Powershell

 

In the next couple of posts I’m going to blog about automating a System Center post-configuration deployment with Powershell. After you have deployed a typical SC environment you still need to do quite a lot of configuration. The following posts will cover how to automate this with Powershell.

 

The first powershell script is to define a specific user account as a sysadmin on a bunch of SQL servers:

[powershell]

_{#Variables
$Servers = @(“srv01.contoso.com”,”srv02.contoso.com”)}

_{#Connect remotely to the server}

_{Foreach($Server in $Servers)
{}

_{Invoke-Command -ComputerName $Server -ScriptBlock {}

_{#Set the system account as sysadmin}

_{[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null}

_{# Create SMO Connections to both SQL Instances
$Svr = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “$server”
$svrole = $svr.Roles | where {$_.Name -eq ‘sysadmin’}
$svrole.AddMember(“User account”)
}
}}

[/powershell]

Thanks,

Alex