Setting SQL permissions with Powershell

less than 1 minute read

 

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

Leave a comment