Automate SQL Server Database Role Assignment with PowerShell and dbatools

Introduction

As a SQL Server DBA, one of the most repetitive administrative tasks is granting user access to multiple databases — especially in environments with dozens or even hundreds of databases.
Instead of manually connecting to each database and assigning roles, you can automate the process with the dbatools PowerShell module.

In this post, we’ll walk through how to automatically grant a user db_datareader and db_datawriter roles across all user databases, while excluding system databases.

Prerequisites

Before running the script:

  • Install the dbatools PowerShell module: Install-Module dbatools -Scope CurrentUser -Force
  • Ensure your account has sufficient permissions (sysadmin or equivalent).
  • Know the SQL instance name and the login you want to grant permissions to.

The PowerShell Script

# Requires dbatools
# Install-Module dbatools -Scope CurrentUser -Force

$SqlInstance = "MyServer\MyInstance"  # Replace with your SQL Server instance
$Login = "MyDomain\MyUser"            # Replace with your Windows or SQL login

# Get all user databases (excluding system DBs)
$UserDatabases = Get-DbaDatabase -SqlInstance $SqlInstance | Where-Object { -not $_.IsSystemObject }

foreach ($db in $UserDatabases) {
    Write-Host "Processing database: $($db.Name)" -ForegroundColor Cyan

    try {
        # Create the user if not already present
        $user = Get-DbaDbUser -SqlInstance $SqlInstance -Database $db.Name -User $Login -ErrorAction SilentlyContinue
        if (-not $user) {
            New-DbaDbUser -SqlInstance $SqlInstance -Database $db.Name -Login $Login -Username $Login -Confirm:$false | Out-Null
        }

        # Grant roles
        Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $db.Name -Role db_datareader -User $Login -Confirm:$false -ErrorAction Stop
        Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $db.Name -Role db_datawriter -User $Login -Confirm:$false -ErrorAction Stop

        Write-Host "✅ Granted db_datareader and db_datawriter in $($db.Name)" -ForegroundColor Green
    }
    catch {
        Write-Warning "Failed to process $($db.Name): $_"
    }
}

Write-Host "Completed assigning roles for $Login on all user databases." -ForegroundColor Green

Explanation

  • Get-DbaDatabase retrieves all databases and filters out system ones.
  • New-DbaDbUser ensures the login exists as a user in each DB.
  • Add-DbaDbRoleMember grants the necessary roles.
  • The script is non-interactive (-Confirm:$false), making it perfect for automation or CI/CD pipelines.

Example Usage

.\Grant-DbRoles.ps1 -SqlInstance "SQL01" -Login "Contoso\User123"

Key Takeaways

  • Save hours by automating repetitive access management tasks.
  • dbatools provides robust error handling and clean PowerShell syntax.
  • Works seamlessly with both Windows and SQL logins.
  • Ideal for onboarding new users or service accounts.

Leave a comment