functions/Get-DbaPermission.ps1

Function 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.
     
See https://msdn.microsoft.com/en-us/library/ms191291.aspx for more information
 
.PARAMETER SqlServer
The SQL Server that you're connecting to.
 
.PARAMETER Credential
Credential object used to connect to the SQL Server as a different user
 
.PARAMETER Databases
Return information for only specific databases
 
.PARAMETER Exclude
Return information for all but these specific databases
 
.PARAMETER Detailed
Shows detailed information
 
.PARAMETER IncludeServerLevel
Shows also information on Server Level Permissions
 
.PARAMETER NoSystemObjects
Excludes all permissions on system securables
 
.NOTES
Author: Klaas Vandenberghe ( @PowerDBAKlaas )
     
dbatools PowerShell module (https://dbatools.io)
Copyright (C) 2016 Chrissy LeMaire
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.
 
     
.LINK
https://dbatools.io/Get-DbaPermission
 
.EXAMPLE
Get-DbaPermission -SqlServer ServerA\sql987
 
Returns a custom object with Server name, Database name, permission state, permission type, grantee and securable
 
.EXAMPLE
Get-DbaPermission -SqlServer ServerA\sql987 -Detailed | Format-Table -AutoSize
 
Returns a formatted table displaying Server, Database, permission state, permission type, grantee, granteetype, securable and securabletype
 
.EXAMPLE
Get-DbaPermission -SqlServer ServerA\sql987 -NoSystemObjects -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
Get-DbaPermission -SqlServer sql2016 -Databases master
 
Returns a custom object with permissions for the master database
 
#>

    [CmdletBinding()]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlInstance")]
        [string[]]$SqlServer,
        [PsCredential]$Credential,
        [switch]$Detailed,
        [switch]$IncludeServerLevel,
        [switch]$NoSystemObjects
    )
    
    DynamicParam
    {
        if ($SqlServer)
        {
            return Get-ParamSqlDatabases -SqlServer $SqlServer[0] -SqlCredential $Credential
        }
    }
    
    BEGIN
    {
        # Convert from RuntimeDefinedParameter object to regular array
        $databases = $psboundparameters.Databases
        $exclude = $psboundparameters.Exclude
        
        if ($NoSystemObjects)
        {
            $ExcludeSystemObjectssql = "WHERE major_id > 0 "
        }
        $ServPermsql = @"
SELECT
      [Server] = @@SERVERNAME
    , [Database] = ''
    , [PermState] = state_desc
    , [PermissionName] = permission_name
    , [SecurableType] = COALESCE(O.type_desc,sp.class_desc)
    , [Securable] = CASE WHEN class = 100 THEN @@SERVERNAME
                            WHEN class = 105 THEN OBJECT_NAME(major_id)
                            ELSE OBJECT_NAME(major_id)
                            END
    , [Grantee] = SUSER_NAME(grantee_principal_id)
    , [GranteeType] = pr.type_desc
 
FROM sys.server_permissions sp
    JOIN sys.server_principals pr ON pr.principal_id = sp.grantee_principal_id
    LEFT OUTER JOIN sys.all_objects o ON o.object_id = sp.major_id
 
$ExcludeSystemObjectssql
 
;
"@

        
        $DBPermsql = @"
SELECT
      [Server] = @@SERVERNAME
    , [Database] = DB_NAME()
    , [PermState] = state_desc
    , [PermissionName] = permission_name
    , [SecurableType] = COALESCE(O.type_desc,dp.class_desc)
    , [Securable] = CASE WHEN class = 0 THEN DB_NAME()
                            WHEN class = 1 THEN ISNULL(s.name + '.','')+OBJECT_NAME(major_id)
                            WHEN class = 3 THEN SCHEMA_NAME(major_id) END
    , [Grantee] = USER_NAME(grantee_principal_id)
    , [GranteeType] = pr.type_desc
 
FROM sys.database_permissions dp
    JOIN sys.database_principals pr ON pr.principal_id = dp.grantee_principal_id
    LEFT OUTER JOIN sys.all_objects o ON o.object_id = dp.major_id
    LEFT OUTER JOIN sys.schemas s ON s.schema_id = o.schema_id
 
$ExcludeSystemObjectssql
 
;
"@

    }
    
    PROCESS
    {
        foreach ($servername in $SqlServer)
        {
            try
            {
                $server = Connect-SqlServer -SqlServer $servername -SqlCredential $Credential
            }
            catch
            {
                if ($SqlServer.count -eq 1)
                {
                    throw $_
                }
                else
                {
                    Write-Warning "Can't connect to $servername. Moving on."
                    Continue
                }
            }
            
            if ($server.versionMajor -lt 9)
            {
                Write-Warning "Get-DbaPermission is only supported on SQL Server 2005 and above. Skipping Instance."
                Continue
            }
            
            if ($IncludeServerLevel)
            {
                Write-Debug "T-SQL: $ServPermsql"
                
                $resultTable = $null
                $resultTable = $server.Databases["master"].ExecuteWithResults($ServPermsql).Tables[0]
                foreach ($row in $resultTable)
                {
                    $obj = [PSCustomObject]@{
                        Server = $row.Server
                        Database = ""
                        PermState = $row.PermState
                        PermName = $row.PermissionName
                        Securable = $row.Securable
                        Grantee = $row.Grantee
                        SecurableType = $row.SecurableType
                        GranteeType = $row.GranteeType
                    }
                    if ($detailed) { $obj }
                    else { $obj | Select-Object Server, Database, PermState, PermName, Securable, Grantee }
                }
            }
            
            $dbs = $server.Databases
            
            if ($databases.count -gt 0)
            {
                $dbs = $dbs | Where-Object { $databases -contains $_.Name }
            }
            
            if ($exclude.count -gt 0)
            {
                $dbs = $dbs | Where-Object { $exclude -notcontains $_.Name }
            }
            
            # $dbs = $dbs | Where-Object {$_.IsAccessible}
            
            foreach ($db in $dbs)
            {
                Write-Verbose "Processing $($db.name) on $servername"
                
                if ($db.IsAccessible -eq $false)
                {
                    Write-Warning "The database $($db.name) is not accessible. Skipping database."
                    Continue
                }
                
                
                Write-Debug "T-SQL: $DBPermsql"
                
                $resultTable = $null
                $resultTable = $db.ExecuteWithResults($DBPermsql).Tables[0]
                foreach ($row in $resultTable)
                {
                    $obj = [PSCustomObject]@{
                        Server = $row.Server
                        Database = $row.Database
                        PermState = $row.PermState
                        PermName = $row.PermissionName
                        Securable = $row.Securable
                        Grantee = $row.Grantee
                        SecurableType = $row.SecurableType
                        GranteeType = $row.GranteeType
                    }
                    if ($detailed) { $obj }
                    else { $obj | Select-Object Server, Database, PermState, PermName, Securable, Grantee }
                }
            }
        }
    }
    END
    { }
}