dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Get-DbaPermission

Get a list of Server and Database level 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.

Screenshots

get-dbapermission

Examples

To return a custom object with Server name, Database name, permission state, permission type, grantee and securable

Get-DbaPermission -SqlServer sql2014

To return a formatted table displaying Server, Database, permission state, permission type, grantee, granteetype, securable and securabletype

Get-DbaPermission -SqlServer sql2012 -Detailed | Format-Table -AutoSize

To return 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

Get-DbaPermission -SqlServer sql2016 -NoSystemObjects -IncludeServerLevel

To return a custom object with permissions for the master database

Get-DbaPermission -SqlServer sql2016 -Databases master

Author

This command was created by Klaas Vandenberghe. You can find Klaas on Twitter and his blog.

Get-Help

From PowerShell, execute Get-Help Get-DbaPermission -Detailed for more information on this function.

Source Code

Want to see the source code? View Get-DbaPermission.ps1 on GitHub
 

Related commands