# 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 "
}
}