dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.44k stars 796 forks source link

Get-DbaPermission returns Null in field 'Securable' for Permission releated to SecurableType such as SERVER_PRINCIPAL, ENDPOINT and AVAILABILITY GROUP #9170

Closed uncletimmy3 closed 10 months ago

uncletimmy3 commented 10 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Field 'Securable' is null in Get-DbaPermission output if SecurableType such as SERVER_PRINCIPAL, ENDPOINT and AVAILABILITY GROUP

Steps to Reproduce

Add some permissions to server role 'foobar'

GRANT VIEW DEFINITION ON AVAILABILITY GROUP::[AG01] TO [foobar]
GO

GRANT VIEW DEFINITION ON ENDPOINT::[Dedicated Admin Connection] TO [foobar]
GO

GRANT VIEW DEFINITION ON LOGIN::[sa] TO [foobar]
GO

Get-DbaPermission output

Get-DbaPermission -SqlInstance $someSqlInstance -IncludeServerLevel | ? Grantee -eq 'foobar' | ft

ComputerName   InstanceName SqlInstance         Database PermState PermissionName    SecurableType      Securable           Grantee GranteeType
------------   ------------ -----------         -------- --------- --------------    -------------      ---------           ------- -----------
SERVER-AG1     AG01         SERVER-AG1\AG01          GRANT     VIEW DEFINITION   SERVER_PRINCIPAL                       foobar  SERVER_ROLE
SERVER-AG1     AG01         SERVER-AG1\AG01          GRANT     VIEW DEFINITION   ENDPOINT                               foobar  SERVER_ROLE
SERVER-AG1     AG01         SERVER-AG1\AG01          GRANT     VIEW DEFINITION   AVAILABILITY GROUP                     foobar  SERVER_ROLE

Please confirm that you are running the most recent version of dbatools

2.1.5

Other details or mentions

No response

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      7.2.13
PSEdition                      Core
GitCommitId                    7.2.13
OS                             Microsoft Windows 10.0.19045
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

.NET Framework Version

.NET 6.0.20