Takes permissions from a spreadsheet and assigns them to the SQL Servers. If the logins are SQL Logins it will prompt for the password to be entered.

SQLPermissionsFromSpreadsheet.ps1

$perms = import-excel -Path C:\Temp\spreadsheetName.xlsx -WorksheetName permissions

<#
The spreadsheet should have a worksheet named permissions with the following columns:

Username    Server  Database     Permissions
JessUser    mssql1  database1    db_datareader
UserName2   mssql2  database2    db_datareader, db_datawriter, execute
#>

$perms | % -PV perm -process { $_ } | % {

    $SqlInstance = $perm.server
    $login = $perm.Username
    $database = $perm.Database
    $roles = $perm.Permissions

    if(-not (Get-DbaDatabase -SqlInstance $SqlInstance -Database $database)) {
        Write-Warning ('Could not find {0}.{1}' -f $SqlInstance, $database)
    } else {

        # create login
        if(-not (Get-DbaLogin -SqlInstance $SqlInstance -Login $login)) {
            write-output ('Create login for {0} on {1}' -f $login, $SqlInstance)

            New-DbaLogin -SqlInstance $SqlInstance -Login $login
        } else {
            write-host ('Skipping ... login exists for {0} on {1}' -f $login, $SqlInstance) -ForegroundColor cyan
        }

        # create user
        if(-not (Get-DbaDbUser -SqlInstance $SqlInstance -Database $database -Login $login)) {
            write-output ('Create db user for {0} on {1}.{2}' -f $login, $SqlInstance, $database)

            New-DbaDbUser -SqlInstance $SqlInstance -Database $database -Login $login
        } else {
            write-host  ('Skipping ... db user exists for {0} on {1}.{2}' -f $login, $SqlInstance, $database) -ForegroundColor cyan
        }

        # check in roles
        $roles.split(',').trim() | % -PV role -process { $_ } | % {
            if($role -eq 'execute') {
                # grant execute writes
                write-output ('Grant execute to {0} on {1}.{2}' -f $login, $SqlInstance, $database)
                Invoke-DbaQuery -SqlInstance $SqlInstance -Database $database -Query ('GRANT EXECUTE TO [{0}]' -f $login)
            } else {
                if(-not (Get-DbaDbRoleMember -SqlInstance $SqlInstance -Database $database -Role $role | Where-Object login -eq $login)) {
                    write-output ('Add {0} to {1} on {2}.{3}' -f $login, $role, $SqlInstance, $database)
                    Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $database -Role $role -User $login -confirm:$false
                } else {
                    write-host  ('Skipping ... db user {0} exists in {1} on {2}.{3}' -f $login, $role, $SqlInstance, $database) -ForegroundColor cyan
                }
            }
        }
    }
}