Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Add an existing SQL user to a role for a specific database
Write-Output "Loaded Add-SQLDBUserToRole function"
Function Add-SQLDBUserToRole {
Param (
[string] $server,
[String] $Database ,
[string]$User,
[string]$Role
)
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
$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-Object 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-Object 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"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.