1

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 > 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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.