Automating SQL Server User Removal with PowerShell and dbatools

Introduction

When an employee leaves or a service account is retired, it’s essential to remove their access cleanly and consistently from SQL Server.
Manually revoking access across multiple databases can be error-prone and time-consuming — especially in large environments.

In this post, we’ll look at how to use the dbatools PowerShell module to automatically remove a user from all databases (except system ones) and drop the server-level login, with full logging for audit purposes.

Prerequisites

  • Install dbatools (if not already installed): Install-Module dbatools -Scope CurrentUser -Force
  • Ensure you have sysadmin rights on the SQL instance.
  • Have the login name ready (domain or SQL account).

The PowerShell Script

<#
.SYNOPSIS
Removes a SQL Server login and its users from all user databases.
Works for both domain and SQL logins, with logging.
#>

param(
    [Parameter(Mandatory = $true)]
    [string]$SqlInstance,
    [Parameter(Mandatory = $true)]
    [string]$Login,
    [string]$LogFile = "$(Join-Path $PSScriptRoot ("UserRemovalLog_{0:yyyyMMdd_HHmmss}.txt" -f (Get-Date)))"
)

if (-not (Get-Module -ListAvailable -Name dbatools)) {
    Write-Error "Please install dbatools using: Install-Module dbatools -Scope CurrentUser -Force"
    exit 1
}

function Write-Log {
    param([string]$Message, [string]$Color = "White")
    $timestamp = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
    $logEntry = "[$timestamp] $Message"
    Write-Host $logEntry -ForegroundColor $Color
    Add-Content -Path $LogFile -Value $logEntry
}

Write-Log "=== Starting cleanup for login: $Login on instance: $SqlInstance ===" "Cyan"

$UserDatabases = Get-DbaDatabase -SqlInstance $SqlInstance | Where-Object { -not $_.IsSystemObject }

foreach ($db in $UserDatabases) {
    try {
        $dbName = $db.Name
        $user = Get-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -User $Login -ErrorAction SilentlyContinue
        if ($user) {
            Write-Log "Removing user [$Login] from [$dbName]" "Red"
            Remove-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -User $Login -Confirm:$false -ErrorAction Stop
            Write-Log "✅ Removed from [$dbName]" "Green"
        }
        else {
            Write-Log "User [$Login] not found in [$dbName]" "DarkGray"
        }
    }
    catch {
        Write-Log "⚠️ Failed in [$dbName]: $_" "Yellow"
    }
}

try {
    $loginObj = Get-DbaLogin -SqlInstance $SqlInstance -Login $Login -ErrorAction SilentlyContinue
    if ($loginObj) {
        $loginType = $loginObj.LoginType
        Write-Log "Removing server-level login [$Login] ($loginType)" "Red"
        Remove-DbaLogin -SqlInstance $SqlInstance -Login $Login -Confirm:$false -ErrorAction Stop
        Write-Log "✅ Server-level login removed" "Green"
    }
    else {
        Write-Log "No server-level login [$Login] found" "DarkGray"
    }
}
catch {
    Write-Log "⚠️ Failed to remove login [$Login]: $_" "Yellow"
}

Write-Log "=== Completed cleanup for [$Login] on [$SqlInstance] ===" "Cyan"
Write-Log "Log file saved to: $LogFile" "Gray"

How It Works

  • Get-DbaDatabase lists all user databases.
  • Get-DbaDbUser / Remove-DbaDbUser checks for and removes the user from each DB.
  • Get-DbaLogin / Remove-DbaLogin cleans up the login from the instance.
  • All actions are written to a timestamped .txt log for compliance or auditing.

Example Usage

.\Remove-DbUserFromAllDatabases.ps1 -SqlInstance "SQLPROD01" -Login "Contoso\User123"

You can also specify a custom log path:

.\Remove-DbUserFromAllDatabases.ps1 -SqlInstance "SQLPROD01" -Login "appuser" -LogFile "C:\Logs\UserCleanup.txt"

Key Takeaways

  • Fully automated and non-interactive — perfect for offboarding workflows.
  • Handles both Windows and SQL logins gracefully.
  • Creates a detailed audit log for every action taken.
  • Safe to re-run — it skips users or logins that don’t exist.

Leave a comment