Why doesn't invoke-sqlcmd return ALL the query data

Question

I run the following query in SQL Server 2014:

SELECT
    dp.class_desc, dp.permission_name, dp.state_desc,
    ObjectName = OBJECT_NAME(major_id), 
    GranteeName = grantee.name, GrantorName = grantor.name
FROM 
    sys.database_permissions dp
JOIN
    sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
JOIN 
    sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id

And I get all the rows that I expect. I created test database users and roles and I see them.

When I run it in powershell:

(invoke-sqlcmd 'select dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id -ServerInstance Blah\blah)

I don't see my test database users/roles in the output. I'm using Windows authentication. Same user is running the PowerShell command and connecting via SSMS. Also, in my PowerShell output, I see disabled accounts. I don't see when I run the query in the SSMS.

Any suggestions on how I can get the same results from PowerShell as I get from SSMS?

It was a copy/paste error into the webpage. The single quote was in the code. Thanks — boblewis, Jan 14 '20 at 00:11

score 1 · Accepted Answer · answered Jan 14 '20 at 01:22

I don't think you running both commands against the same Database. i.e. in SSMS you are running it against your desired database, but in your Invoke-SqlCmd command you don't specify which the database you are running against. It will default to the database master, which would be why you see "disabled" accounts. Try specifying the -Database parameter.

(invoke-sqlcmd 'select dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id' -ServerInstance 'Blah\blah' -Database 'MyDatabase')

Thank you that was it. SSMS was showing me all the databases. The Invoke-sqlcmd was just showing master. I added a database name to the command and saw my test users and roles. I upvoted you, but I don't have enough points to change the vote — boblewis, Jan 14 '20 at 03:41
Excellent. As the asker, you have the special privilege to mark the answer as [Accepted](https://meta.stackexchange.com/a/5235/226783) which helps out. Thanks! — HAL9256, Jan 14 '20 at 17:43

Why doesn't invoke-sqlcmd return ALL the query data

1 Answers1