more membership in "master.sys.login_token" than "net group"

Hi.

I'm investigating whether I can lock down a feature exclusively to only those who belong to a certain domain group.

From what I understand, the master.sys.login_token view holds information on all the groups I'm a part of. However, I'm looking at the output from

SELECT * FROM master.sys.login_token

and see a number of groups that I can't verify I have access to. I have even run the DOS command "net group <domaingroup> /dom" and don't see myself listed in its output.

Is this normal? Is the output a culmination of everyone logged in at the time?

@.@.version = 9.00.2047.00

It will show your login along with any roles you are a member of, along with any Windows groups you are a member of.

For example, I have a test system with 3 instances of SQL Server installed. Each instance of SQL Server has 3 local Windows groups created. The service account for each instance is the same for all SQL Server services. It is also currently configured as a localadministrator. That account is what I used to login to this instance and it is also a member of the sysadmin role. (Think an XP machine with a single account that I log in with and also run the services under.) Querying this view shows me the Windows account, sysadmin, public, and every Windows group on the machine that this account is a member of. The vast majority of those groups are not principals in this particular instance of SQL Server. (It has me really confused as to why it would show that.)

So, based on this, it is going to show:

1. Your login

2. Any server roles you are a member of

3. Any Windows groups on the machine that your account is a member of (whether or not they are even principals in the instance you are querying)

So, yes, you could use the sys.login_token table to restrict access based on a group membership. I'm not exactly sure what you mean about locking down a feature. But, a more straightforward way for managing the security permissions is to add the necessary group to the SQL Server and grant permissions on the objects you want them to access and then revoke access from everyone else. But, that might not meet your needs such as locking out the feature within an application.

|||

Thanks for your quick response.... I guess what's shocking me more than anything is the fact that sys.login_token is suggesting I'm in groups I don't believe I belong in.

I've run this statement: SELECT name FROM master.sys.login_token

and get these results....

public
sysadmin
DOMAIN\Domain Users
\Everyone
BUILTIN\Users
BUILTIN\Administrators
NT AUTHORITY\NETWORK
NT AUTHORITY\Authenticated Users
DOMAIN\G_Group1
DOMAIN\G_Group2
DOMAIN\G_Group3
DOMAIN\G_Group4
DOMAIN\G_Group5
DOMAIN\G_Group6
NT AUTHORITY\NTLM Authentication

I agree with most of these results... but when I go to a Command Window and type

net group G_Group5 /dom

I am not included in the output.

When I type

net user MyUserName /dom

I don't see G_Group5 in my output.

|||

It turns out DOMAIN\G_Group1 is a member of DOMAIN\G_Group5

net group doesn't post group info, neither "membership" nor "members of."

sql