Find-DbaUserObject

Synopsis

Finds SQL Server objects owned by users other than sa or dbo, or searches for objects owned by a specific user pattern.

Description

Scans SQL Server instances to identify objects with non-standard ownership, which is critical for security auditing and user management.
When removing user accounts or performing security reviews, you need to know what objects they own to avoid breaking dependencies.
This function searches databases, SQL Agent jobs, credentials, proxies, endpoints, server roles, schemas, database roles, assemblies, and synonyms.
Use the Pattern parameter to search for objects owned by a specific user, or run without it to find all user-owned objects that aren’t owned by system accounts.

Syntax

Find-DbaUserObject
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Pattern] <String>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Find-DbaUserObject -SqlInstance DEV01 -Pattern ad\stephen

Searches user objects for owner ad\stephen

Example: 2

PS C:\> Find-DbaUserObject -SqlInstance DEV01 -Verbose

Shows all user owned (non-sa, non-dbo) objects and verbose output

Required Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input

Optional Parameters

-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.

-Pattern

Searches for objects owned by accounts matching this regex pattern. Use this when looking for objects owned by a specific user or group of users.
When omitted, finds all objects not owned by system accounts (sa/dbo). Supports Windows domain accounts like ‘DOMAIN\username’ or SQL logins.

-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.

Outputs

PSCustomObject

Returns one object per user-owned SQL Server object found. The function scans multiple object types across the instance and all accessible databases, so you may receive many objects from a single instance.

Properties:

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name (ServiceName from SMO)
• SqlInstance: The full SQL Server instance name (computer\instance format)
• Type: The category of object found. Possible values include:

• Database * Agent Job * Credential * Proxy * Agent Step * Endpoint * Server Role * Schema * Database Role * Database Assembly * Database Synonyms

• Owner: The login or user account that owns the object (string format for logins, domain\username for Windows accounts)
• Name: The name of the object
• Parent: The name of the parent container for the object (e.g., server name for databases, job name for job steps, database name for schemas)