For MS SQL, see SQL Cheat Sheet

Put column names in double quotes.

SELECT
      "ApiResources"."Name" AS ApiResourceName
    , "ApiResources"."DisplayName" AS ApiResourceDisplayName
    , "ApiScopes"."Name" AS ScopeName
    , "ApiScopes"."DisplayName" AS ScopeDisplayName
    , "ApiResources".*
FROM
    config."ApiResources"
LEFT JOIN
    config."ApiScopes"
    ON "ApiResources"."Id" = "ApiScopes"."ApiResourceId"
WHERE 1 = 1
    AND "ApiScopes"."Name" = 'xero_identity_test'

Use LIMIT n instead.

SELECT *
FROM   IDENTITY.users
LIMIT  10