Author | Ben Miller (@DBAduck) |
Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Get-DbaDbRole on GitHub.
Want to see the Bill Of Health for this command? Check out Get-DbaDbRole.
Retrieves database roles from SQL Server instances for security auditing and permission analysis.
Retrieves all database roles (both fixed and custom) from one or more SQL Server databases, returning detailed role information for security audits and compliance reporting. This function examines the roles collection in each accessible database, allowing you to identify custom roles, exclude built-in fixed roles, or focus on specific roles by name. Essential for documenting role structures across environments, troubleshooting permission issues, and ensuring consistent security configurations during migrations or standardization projects.
Get-DbaDbRole
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Database] <String[]>]
[[-ExcludeDatabase] <String[]>]
[[-Role] <String[]>]
[[-ExcludeRole] <String[]>]
[-ExcludeFixedRole]
[[-InputObject] <Database[]>]
[-EnableException]
[<CommonParameters>]
PS C:\> Get-DbaDbRole -SqlInstance localhost
Returns all database roles in all databases on the local default SQL Server instance
PS C:\> Get-DbaDbRole -SqlInstance localhost, sql2016
Returns all roles of all database(s) on the local and sql2016 SQL Server instances
PS C:\> $servers = Get-Content C:\servers.txt
PS C:\> $servers | Get-DbaDbRole
Returns roles of all database(s) for every server in C:\servers.txt
PS C:\> Get-DbaDbRole -SqlInstance localhost -Database msdb
Returns roles of the database msdb on localhost.
PS C:\> Get-DbaDbRole -SqlInstance localhost -Database msdb -ExcludeFixedRole
Returns all non-fixed roles in the msdb database on localhost.
PS C:\> Get-DbaDbRole -SqlInstance localhost -Database msdb -Role 'db_owner'
Returns the db_owner role in the msdb database on localhost.
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.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies which databases to examine for role information. Accepts wildcards for pattern matching.
Use this when you need to audit roles in specific databases rather than scanning all databases on the instance.
Particularly useful for focusing on user databases while skipping system databases, or for compliance audits of specific applications.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Excludes specified databases from role enumeration. Accepts wildcards for pattern matching.
Use this to skip databases you don't need to audit, such as development databases during production security reviews.
Commonly used to exclude system databases or databases with known standard configurations.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies which database roles to retrieve by name. Accepts wildcards for pattern matching.
Use this when investigating specific roles across databases, such as checking for custom application roles or finding all instances of a particular role name.
Particularly useful for security audits focusing on elevated permissions like 'db_owner' or custom admin roles.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Excludes specified roles from the results by name. Accepts wildcards for pattern matching.
Use this to filter out roles you're not interested in, such as excluding standard fixed roles when focusing on custom application roles.
Helpful for reducing noise in reports when you want to see only non-standard or suspicious role configurations.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Excludes all built-in fixed database roles from the results, showing only custom user-defined roles.
Use this when auditing custom role implementations or when you need to focus on application-specific security configurations.
Fixed roles like db_owner, db_datareader, and db_datawriter are filtered out, along with the public role.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Accepts database objects from Get-DbaDatabase through the pipeline for role enumeration.
Use this when you need to chain database selection criteria with role analysis, such as filtering databases by size, compatibility level, or other properties first.
Allows for more complex filtering scenarios than the basic Database parameter provides.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
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.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |