# encoding: ascii
# api: powershell
# title: Add SQL Account to Serve
# description: #############################################################################################
# version: 0.1
# type: function
# author: SQLDBAwithabeard
# license: CC0
# function: Add-SQLAccountToSQLRole
# x-poshcode-id: 4461
# x-derived-from-id: 4475
# x-archived: 2014-08-20T09:50:46
# x-published: 2014-09-11T16:56:00
#
# #
# NAME: Add-SQLAccountToSQLRole.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:11/09/2013
# #
# COMMENTS: Load function to create a SQL user and add them to a server role
#
#############################################################################################
#
# NAME: Add-SQLAccountToSQLRole.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:11/09/2013
#
# COMMENTS: Load function to create a sql user and add them to a server role
#
# USAGE: Add-SQLAccountToSQLRole FADE2BLACK Test Password01 dbcreator
# Add-SQLAccountToSQLRole FADE2BLACK Test Password01 public
Function Add-SQLAccountToSQLRole ([String]$Server, [String] $User, [String]$Password, [String]$Role)
{
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
# Check if Role entered Correctly
if($svr.Roles.name -notcontains $Role)
{
Write-Host " $Role is not a valid Role on $Server"
}
else
{
#Check if User already exists
if($svr.Logins.Contains($User))
{
$SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login $Server, $User
$LoginName = $SQLUser.Name
if($Role -notcontains "public")
{
$svrole = $svr.Roles | where {$_.Name -eq $Role}
$svrole.AddMember($LoginName)
}
}
else
{
$SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login $Server, $User
$SqlUser.LoginType = 'SqlLogin'
$SqlUser.PasswordExpirationEnabled = $false
$SqlUser.Create($Password)
$LoginName = $SQLUser.Name
if($Role -notcontains "public")
{
$svrole = $svr.Roles | where {$_.Name -eq $Role}
$svrole.AddMember($LoginName)
}
}
}
}