Test-DbaDbOwner

Want to see the source code for this command? Check out Test-DbaDbOwner on GitHub.
Want to see the Bill Of Health for this command? Check out Test-DbaDbOwner.

Synopsis

Identifies databases with incorrect ownership for security compliance and best practice enforcement.

Description

This function compares the current owner of each database against a target login and returns only databases that do NOT match the expected owner. By default, it checks against 'sa' (or the renamed sysadmin account if 'sa' was changed), but you can specify any valid login.

This addresses a common security compliance requirement where databases should be owned by a specific account rather than individual user accounts. Mismatched ownership can cause issues with scheduled jobs, maintenance plans, and security policies.

The function automatically detects if the 'sa' account was renamed and uses the actual sysadmin login name. It returns detailed information including current owner, target owner, and ownership status for easy identification of databases requiring ownership changes.

Best Practice reference: http://weblogs.sqlteam.com/dang/archive/2008/01/13/Database-Owner-Troubles.aspx

Syntax

Test-DbaDbOwner
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-TargetLogin <String>]
    [-InputObject <Database[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaDbOwner -SqlInstance localhost

Returns all databases where the owner does not match 'sa'.

Example: 2

PS C:\> Test-DbaDbOwner -SqlInstance localhost -TargetLogin 'DOMAIN\account'

Returns all databases where the owner does not match 'DOMAIN\account'.

Example: 3

PS C:\> Get-DbaDatabase -SqlInstance localhost -OnlyAccessible | Test-DbaDbOwner

Gets only accessible databases and checks where the owner does not match 'sa'.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to check for ownership compliance. Accepts wildcards for pattern matching.
Use this when you need to audit ownership for specific databases rather than all databases on the instance.

-ExcludeDatabase

Specifies databases to skip during the ownership compliance check. Accepts wildcards for pattern matching.
Useful for excluding system databases or databases with intentionally different ownership from standard policy.

-TargetLogin

Specifies the expected database owner login for compliance checking. Defaults to 'sa' or the renamed sysadmin account if 'sa' was changed.
Use this to enforce organizational standards where databases should be owned by a service account or specific login rather than individual users.

-InputObject

Accepts database objects piped from Get-DbaDatabase for ownership verification.
Use this to check ownership on a pre-filtered set of databases or when chaining with other database operations.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.