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
	{ }
}