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

Please note that documentation and command names may be out of date while we work furiously towards 1.0


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