CODEWITHSUNDEEP
sql-serverpowershellinvoke-sqlcmd
boblewis
boblewis

Reputation: 93

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

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?

Upvotes: 0

Views: 518

Answers (1)

HAL9256
HAL9256

Reputation: 13453

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

Upvotes: 1

Related Questions