Creating a Windows User and adding to a SQL Server Role with PowerShell

image

The function does some simple error checking. If the login already exists on the server it will just add it to the role and if the role has been mistyped it will let you know. It does this by checking if the Role object is Null for the Roles and the Contains Method for the Logins

image

Add-WindowsAccountToSQLRole FADE2BLACK ‘FADE2BLACK\Test’ public
###########################################################
#
# NAME: Add-WindowsAccountToSQLRole.ps1
# AUTHOR: Rob Sewell https://blog.robsewell.com
# DATE:11/09/2013
#
# COMMENTS: Load function to create a windows user and add     them to a server role
#
# USAGE: Add-WindowsAccountToSQLRole FADE2BLACK     'FADE2BLACK\Test' dbcreator
#        Add-WindowsAccountToSQLRole FADE2BLACK     'FADE2BLACK\Test' public

Function Add-WindowsAccountToSQLRole ([String]$Server,     [String] $User, [String]$Role) {

    $Svr = New-Object ('Microsoft.SqlServer.Management.Smo.    Server') $server

    # Check if Role entered Correctly
    $SVRRole = $svr.Roles[$Role]
    if ($SVRRole -eq $null) {
        Write-Output " $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 = 'WindowsUser'
            $SqlUser.Create()
            $LoginName = $SQLUser.Name
            if ($Role -notcontains "public") {
                $svrole = $svr.Roles | where {$_.Name -eq     $Role}
                $svrole.AddMember("$LoginName")
            }
        }
    }
}
Built with Hugo
Theme Stack designed by Jimmy