commands

^

Get-DbaPermission

Author Klaas Vandenberghe (@PowerDBAKlaas)
Availability Windows, Linux, macOS

 

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.

Alias
Required True
Pipeline true (ByValue)
Default Value

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.

Alias
Required False
Pipeline false
Default Value
-Database

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

Alias
Required False
Pipeline false
Default Value
-ExcludeDatabase

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

Alias
Required False
Pipeline false
Default Value
-IncludeServerLevel

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

Alias
Required False
Pipeline false
Default Value False
-ExcludeSystemObjects

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

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False