So I have a query that works fine within SQL server management studio.
The query itself is pulling user permissions from all databases on the server.
However when I execute the query from within powershell, it only returns a single item from the master database.
I’ve tried both the month of lunches function, as well as dbatools, putting the query into the script itself, as well as calling it from a .txt and .sql file.
Not sure what i am actually missing here.
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; WITH perms_cte as ( select DB_NAME() AS DataBaseName,USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc AS permission_state_desc from sys.database_permissions p inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id ) --role members SELECT @@servername as Hostname, DB_NAME() AS DataBaseName,rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name FROM perms_cte p right outer JOIN ( select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name, user_name(role_principal_id) as role_name--,* from sys.database_role_members rm INNER JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id ) rm ON rm.role_principal_id = p.principal_id --where p.permission_name != "select" order by 1 '