< Back
Get-DbaPermission
Post
NAME Get-DbaPermission
SYNOPSIS
Get a list of Server and Database level permissions
SYNTAX
Get-DbaPermission [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [-IncludeServerLevel
<Switch>] [-ExcludeSystemObjects <Switch>] [-EnableException <Switch>] [<CommonParameters>]
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.
See https://msdn.microsoft.com/en-us/library/ms191291.aspx for more information
PARAMETERS
-Database [<System.Object[]>]
Specifies one or more database(s) to process. If unspecified, all databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
If this switch is enabled exceptions will be thrown to the caller, which will need to perform its own
exception processing. Otherwise, the function will try to catch the exception, interpret it and provide a
friendly error message.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Specifies one or more database(s) to exclude from processing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystemObjects [<Switch>]
If this switch is enabled, permissions on system securables will be excluded.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeServerLevel [<Switch>]
If this switch is enabled, information about Server Level Permissions will be output.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. Defaults to localhost.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Permissions, Databases
Author: Klaas Vandenberghe (@PowerDBAKlaas)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Get-DbaPermission
SYNOPSIS
Get a list of Server and Database level permissions
SYNTAX
Get-DbaPermission [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [-IncludeServerLevel
<Switch>] [-ExcludeSystemObjects <Switch>] [-EnableException <Switch>] [<CommonParameters>]
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.
See https://msdn.microsoft.com/en-us/library/ms191291.aspx for more information
PARAMETERS
-Database [<System.Object[]>]
Specifies one or more database(s) to process. If unspecified, all databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
If this switch is enabled exceptions will be thrown to the caller, which will need to perform its own
exception processing. Otherwise, the function will try to catch the exception, interpret it and provide a
friendly error message.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Specifies one or more database(s) to exclude from processing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystemObjects [<Switch>]
If this switch is enabled, permissions on system securables will be excluded.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeServerLevel [<Switch>]
If this switch is enabled, information about Server Level Permissions will be output.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. Defaults to localhost.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Permissions, Databases
Author: Klaas Vandenberghe (@PowerDBAKlaas)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Get-DbaPermission