Issues with runnging sql query with powershell

Welcome Forums General PowerShell Q&A Issues with runnging sql query with powershell

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
9 months ago.

  • Author
    Posts
  • #93823

    Participant
    Points: 14
    Rank: Member

    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
    '
    
    
  • #93832

    Participant
    Points: 49
    Rank: Member

    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
    
    
  • #93840

    Participant
    Points: 0
    Rank: Member

    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

    • #93843

      Participant
      Points: 0
      Rank: Member

      Invoke-SQLCMD does handle the multiple resultsets

  • #93841

    Participant
    Points: 14
    Rank: Member

    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

The topic ‘Issues with runnging sql query with powershell’ is closed to new replies.