Get-DbaDbRoleMember

Synopsis

Retrieves all users and nested roles that are members of database roles across SQL Server instances

Description

This function enumerates the membership of database roles, showing which users and nested roles belong to each role. Essential for security audits, permission troubleshooting, and compliance reporting, it reveals the complete role hierarchy within your databases. By default, system users are excluded to focus on business-relevant accounts, but you can include them for comprehensive security reviews. The function works across multiple instances and databases simultaneously, making it perfect for enterprise-wide role membership documentation and access reviews.

Syntax

Get-DbaDbRoleMember
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [[-Role] <String[]>]
    [[-ExcludeRole] <String[]>]
    [-ExcludeFixedRole]
    [-IncludeSystemUser]
    [[-InputObject] <Object[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbRoleMember -SqlInstance localhost

Returns all members of all database roles on the local default SQL Server instance

Example: 2

PS C:\> Get-DbaDbRoleMember -SqlInstance localhost, sql2016

Returns all members of all database roles on the local and sql2016 SQL Server instances

Example: 3

PS C:\> $servers = Get-Content C:\servers.txt
PS C:\> $servers | Get-DbaDbRoleMember

Returns all members of all database roles for every server in C:\servers.txt

Example: 4

PS C:\> Get-DbaDbRoleMember -SqlInstance localhost -Database msdb

Returns non-system members of all roles in the msdb database on localhost.

Example: 5

PS C:\> Get-DbaDbRoleMember -SqlInstance localhost -Database msdb -IncludeSystemUser -ExcludeFixedRole

Returns all members of non-fixed roles in the msdb database on localhost.

Example: 6

PS C:\> Get-DbaDbRoleMember -SqlInstance localhost -Database msdb -Role 'db_owner'

Returns all members of the db_owner role in the msdb database on localhost.

Example: 7

PS C:\> $roles = Get-DbaDbRole -SqlInstance localhost -Database msdb -Role 'db_owner'
PS C:\> $roles | Get-DbaDbRoleMember

Returns all members of the db_owner role in the msdb database on localhost.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server 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 analyze for role membership. Accepts wildcards for pattern matching.
Use this to focus on specific databases rather than scanning all databases on the instance. Helpful when you only need role membership data for particular applications or business units.

-ExcludeDatabase

Excludes specific databases from role membership analysis. Supports wildcards for pattern matching.
Use this to skip system databases like tempdb or databases under maintenance when performing enterprise-wide role audits.

-Role

Limits the analysis to specific database roles by name. Accepts wildcards for pattern matching.
Use this when investigating membership of particular roles like ‘db_owner’, ‘db_datareader’, or custom application roles during security reviews or troubleshooting.

-ExcludeRole

Excludes specific database roles from the membership analysis. Supports wildcards for pattern matching.
Use this to filter out roles you’re not interested in, such as excluding ‘public’ role or application-specific roles during focused security audits.

-ExcludeFixedRole

Excludes members of SQL Server’s built-in database roles like db_owner, db_datareader, db_datawriter, etc.
Use this when you want to focus only on custom application roles and their memberships, filtering out the standard SQL Server role assignments.

-IncludeSystemUser

Includes SQL Server system users like ‘dbo’, ‘guest’, ‘sys’, and ‘INFORMATION_SCHEMA’ in the results.
Use this for comprehensive security audits or when troubleshooting system-level permission issues. Normally these accounts are excluded to focus on business user accounts.

-InputObject

Accepts piped objects from Get-DbaDbRole, Get-DbaDatabase, or SQL Server instances for processing.
Use this to chain commands together, such as first filtering roles with Get-DbaDbRole then analyzing their membership, or to process multiple database objects efficiently.

-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 member (user or nested role) found in each database role.

Properties:

• ComputerName: The name of the computer where the SQL Server instance is running
• InstanceName: The name of the SQL Server instance
• SqlInstance: The full SQL Server instance name in the format ComputerName\InstanceName
• Database: The database name containing the role
• Role: The name of the database role
• UserName: The name of the user account (populated when the member is a user; $null when the member is a nested role)
• Login: The SQL Server login associated with the user (populated for user members; $null for nested roles)
• MemberRole: The name of the nested role (populated when the member is another role; $null when the member is a user)
• SmoRole: The SMO DatabaseRole object representing the parent role
• SmoUser: The SMO User object (populated for user members; $null for nested role members)
• SmoMemberRole: The SMO DatabaseRole object for nested role members ($null for user members) Use Select-Object to filter properties if you only need specific information, or access SMO objects directly for advanced operations.