# encoding: ascii
# api: powershell
# title: sysadmin
# description: #############################################################################################
# version: 0.1
# type: function
# author: Rob Sewell
# license: CC0
# function: Add-UserToRole
# x-poshcode-id: 6015
# x-archived: 2016-08-15T07:47:09
# x-published: 2016-09-15T19:42:00
#
# #
# NAME: Add-UserToRole.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:11/09/2013
# #
# COMMENTS: Load function to add user or group to a role on a database
# #
# USAGE: Add-UserToRole fade2black Aerosmith Test db_owner
#
#############################################################################################
#
# NAME: Add-UserToRole.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:11/09/2013
#
# COMMENTS: Load function to add user or group to a role on a database
#
# USAGE: Add-UserToRole fade2black Aerosmith Test db_owner
#        

Function Add-UserToRole ([string] $server, [String] $Database , [string]$User, [string]$Role)
{
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
#Check Database Name entered correctly
$db = $svr.Databases[$Database]
    if($db -eq $null)
        {
        Write-Host " $Database is not a valid database on $Server"
        Write-Host " Databases on $Server are :"
        $svr.Databases|select name
        break
        }
#Check Role exists on Database
        $Rol = $db.Roles[$Role]
    if($Rol -eq $null)
        {
        Write-Host " $Role is not a valid Role on $Database on $Server  "
        Write-Host " Roles on $Database are:"
        $db.roles|select name
        break
        }
    if(!($svr.Logins.Contains($User)))
        {
        Write-Host "$User not a login on $server create it first"
        break
        }
    if (!($db.Users.Contains($User)))
        {
        # Add user to database

        $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
        $usr.Login = $User
        $usr.Create()

        #Add User to the Role
        $Rol = $db.Roles[$Role]
        $Rol.AddMember($User)
        Write-Host "$User was not a login on $Database on $server"
        Write-Host "$User added to $Database on $Server and $Role Role"
        }
        else
        {
         #Add User to the Role
        $Rol = $db.Roles[$Role]
        $Rol.AddMember($User)
        Write-Host "$User added to $Role Role in $Database on $Server "
        }
}
