Get-DbaUserPermission misses database role assignments

[pagerwho]

Within the Get-DbaUserPermission.ps1 file it appears that the alias is missing from the third value of the $dbSQL resultset which causes the RoleSecureableClass to appear blank. I was able to confirm this issue exists in version 0.732. This issue was confirmed corrected by adjusting the T-SQL as below:

SELECT 'DB ROLE MEMBERS' AS type , Member , ISNULL(Role, 'None') AS [Role/Securable/Class] , --INSERTED PROPER ALIAS HERE ' ' AS [Schema/Owner] , ' ' AS [Securable] , ' ' AS [Grantee Type] , ' ' AS [Grantee] , ' ' AS [Permission] , ' ' AS [State] , ' ' AS [Grantor] , ' ' AS [Grantor Type] , ' ' AS [Source View] FROM tempdb.[STIG].[database_role_members] UNION SELECT DISTINCT 'DB SECURABLES' AS Type , ISNULL(drm.member, 'None') AS [Role/Securable/Class] , dp.[Securable Type or Class] COLLATE SQL_Latin1_General_CP1_CI_AS , dp.[Schema/Owner] , dp.Securable , dp.[Grantee Type] COLLATE SQL_Latin1_General_CP1_CI_AS , dp.Grantee , dp.Permission COLLATE SQL_Latin1_General_CP1_CI_AS , dp.State COLLATE SQL_Latin1_General_CP1_CI_AS , dp.Grantor , dp.[Grantor Type] COLLATE SQL_Latin1_General_CP1_CI_AS , dp.[Source View] FROM tempdb.[STIG].[database_role_members] drm LEFT JOIN tempdb.[STIG].[database_permissions] dp ON ( drm.member = dp.grantee OR drm.role = dp.grantee ) WHERE dp.Grantor IS NOT NULL AND [Schema/Owner] <> 'sys'

[stale]

This issue has been automatically marked as stale because it has not had recent activity. If you would like this issue to remain open:

• Verify that you can still reproduce the issue in the latest version of dbatools
• Comment that the issue is still reproducible and include:
  • What version of dbatools you reproduced the issue on
  • What OS and version you reproduced the issue on
  • What steps you followed to reproduce the issue