Author Posts

February 16, 2018 at 2:49 pm

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
'

February 16, 2018 at 3:43 pm

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

February 16, 2018 at 3:54 pm

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

February 16, 2018 at 3:57 pm

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

February 16, 2018 at 3:59 pm

Invoke-SQLCMD does handle the multiple resultsets