# encoding: ascii
# api: powershell
# title: Add User to database rol
# description: #############################################################################################
# version: 0.1
# type: function
# author: SQLDBAwithabeard
# license: CC0
# function: Add-UserToRole
# x-poshcode-id: 6765
# x-archived: 2017-03-12T06:17:22
# x-published: 2017-03-02T12:09: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
    if($svr.Databases.name -notcontains $Database)
        {
        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
        $db = $svr.Databases[$Database]
    if($db.Roles.name -notcontains $Role)
        {
        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 "
        }
}
