Get-DbaPermission

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

Synopsis

Get a list of Server and Database level permissions

Description

Retrieves a list of permissions

Permissions link principals to securables.
Principals exist on Windows, Instance and Database level.
Securables exist on Instance and Database level.
A permission state can be GRANT, DENY or REVOKE.
The permission type can be SELECT, CONNECT, EXECUTE and more.
The CONTROL permission is also returned for dbo users, db_owners, and schema owners.
To see server-level implicit permissions via fixed roles run the following command: Get-DbaServerRole -SqlInstance serverName | Select-Object *

See https://msdn.microsoft.com/en-us/library/ms191291.aspx for more information

Syntax

Get-DbaPermission
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [-IncludeServerLevel]
    [-ExcludeSystemObjects]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaPermission -SqlInstance ServerA\sql987

Returns a custom object with Server name, Database name, permission state, permission type, grantee and securable.

Example: 2

PS C:\> Get-DbaPermission -SqlInstance ServerA\sql987 | Format-Table -AutoSize

Returns a formatted table displaying Server, Database, permission state, permission type, grantee, granteetype, securable and securabletype.

Example: 3

PS C:\> Get-DbaPermission -SqlInstance ServerA\sql987 -ExcludeSystemObjects -IncludeServerLevel

Returns a custom object with Server name, Database name, permission state, permission type, grantee and securable
in all databases and on the server level, but not on system securables.

Example: 4

PS C:\> Get-DbaPermission -SqlInstance sql2016 -Database master

Returns a custom object with permissions for the master database.

Required Parameters

-SqlInstance

The target SQL Server instance or instances. Defaults to localhost.

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.

-Database

Specifies one or more database(s) to process. If unspecified, all databases will be processed.

-ExcludeDatabase

Specifies one or more database(s) to exclude from processing.

-IncludeServerLevel

If this switch is enabled, information about Server Level Permissions will be output.

-ExcludeSystemObjects

If this switch is enabled, permissions on system securables will be excluded.

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