Issues with runnging sql query with powershell

PowerShell Help
1

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.
query follows:

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
'
2

Works fine for me…once the " have been preceeded by `


$Q = "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
'"

Invoke-SqlCmd -ServerInstance "myinstance" -Query $q
3

The query you posted returns multiple resultsets (as per sp_MSforeachdb) . You are only seeing the first of several (one for each database) I modified the query you posted and used the DBATOOLS framework to get results from all databases

Import-module DBATOOLS
$DataBases=Get-DbaDatabase -SqlInstance SERVERNAME| select name
foreach ($Database in $DataBases)
{
Invoke-DbaSqlCmd -SqlInstance SERVERNAME -Database $Database.name -File resultset.sql
}

Hope that helps (

Joe E O

4

wow, I am not sure what I had messed up in the code itself, I had those double quotes commented out, in fact went so far as to try and do the ` on all the spcieal chars.

oh well, thanks for looking Iian.

should make our auditors happier to do this via script vs a bunch of manual steps

5

Invoke-SQLCMD does handle the multiple resultsets