Query of sys.login_token doesn't include particular windows group while connect within workgroup I had two SQL Server Logins which correspond to two windows groups:

MachineName\MyAppAmdin
MachineName\MyAppUser


And I use

select principal_id, name, [type], usage
from sys.login_token
where principal_id &gt; 0


to check if the current user is a member of any of these windows groups.

When the machine is under domain it will list

+--------------+---------------------------------+---------------+---------------+
| principal_id |              name               |     type      |     usage     |
+--------------+---------------------------------+---------------+---------------+
|            2 | public                          | SERVER ROLE   | GRANT OR DENY |
|            3 | sysadmin                        | SERVER ROLE   | GRANT OR DENY |
|          260 | MachineName\SQL Server Services | WINDOWS GROUP | GRANT OR DENY |
|          309 | MachineName\MyAppUser           | WINDOWS GROUP | GRANT OR DENY |
|          325 | MachineName\MyAppAmdin          | WINDOWS GROUP | GRANT OR DENY |
+--------------+---------------------------------+---------------+---------------+


as expected.

However, when I try another the machine which is under workgroup it only lists

+--------------+---------------------------------+---------------+---------------+
| principal_id |              name               |     type      |     usage     |
+--------------+---------------------------------+---------------+---------------+
|            2 | public                          | SERVER ROLE   | GRANT OR DENY |
|            3 | sysadmin                        | SERVER ROLE   | GRANT OR DENY |
|          260 | MachineName\SQL Server Services | WINDOWS GROUP | GRANT OR DENY |
+--------------+---------------------------------+---------------+---------------+


Even the account is actually a member of MyAppUser or MyAppAdmin.

Well, perhaps there are other factors I didn't notice but so far workgroup vs domain is the only difference I knew.

Is it supposed to work this way or I got something wrong?
The problem solved after I remove logins and associated users in SQL Server then add them back again.
Have no clue what went wrong but a record for people who might run into the same issue.