I'm only allowed to use certain fields in SOQL run through sfdx

Question

I'm working on a MacBook Pro, so I log into production via a browser, then I open a Terminal window and I authenticate:

sfdx auth:device:login -r https://blue.my.salesforce.com

I type in the code and I am successfully logged in.

Now I can run some SOQL queries:

sfdx force:data:soql -t -q "select Id, Username from User where  lastname = 'Ham'" -r csv > results.csv

This works great.

However, if I do this:

sfdx force:data:soql -t -q "select Id, Username, Email from User where  lastname = 'Ham'" -r csv > results.csv

or:

sfdx force:data:soql -t -q "select Id, Username, FederationId from User where  lastname = 'Ham'" -r csv > results.csv

then I get errors like:

Error: 
select Id, Username, Email
              ^
ERROR at Row:1:Column:22
No such column 'Email' on entity 'User'. If 
you are attempting to use a custom field, be 
sure to append the '__c' after the custom field 
name. Please reference your WSDL or the describe 
call for the appropriate names.

But if I switch to the web browser, and open a Developer Console, then I can run this query:

select Id, Username, Email from User where  lastname = 'Ham'

This works great.

Obviously User has a field called Email, it's one of the built-in fields.

So why can I query Email in a Developer Console but not via the sfdx ClI?

This question/answer focuses on the issue of field level visibility:

"No such column on entity" error for an existing custom field

but again, I can run this query in the developer console and it works. I am a system administrator, I have visibility on almost everything, and all of these SOQL queries work in the Developer Console. So why would it not work in sfdx? I am authenticating as myself.

Answer 1

I ran the same query as you and got the same response. It doesn't seem to be a case sensitivity thing.

Looking at the docs, force:data:soql is deprecated now.

https://developer.salesforce.com/docs/atlas.en-us.sfdx_cli_reference.meta/sfdx_cli_reference/cli_reference_force_data.htm#cli_reference_force_data_soql_query

Instead try using the replacement:

sf data query --query "select Id, Username, Email from User where  lastname = 'Ham'" -r csv > results.csv

This worked for me.