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

Monitor add\remove logins or role membership

SQL Server 2000
How do I monitor who is adding or removing logins from a sql instance?
and/or
How do I monitor who is adding or removing logins from sql server roles?
Thanks in advancedHi
Try this , I found this in my collection , but I don't remember who wrote
the script
select identity(int,1,1) as traceid, a.name as [Database],
ltrim(rtrim(convert(varchar,b.spid))) as spid,
ltrim(rtrim(b.loginame)) as loginame,ltrim(rtrim(b.program_name))
as program_name,ltrim(rtrim(b.hostname))
as hostname into #audittrace from master.dbo.sysprocesses b (nolock) ,
master.dbo.sysdatabases A where
a.dbid = b.dbid and ltrim(rtrim(loginame)) not in
('DBA1','domain\systemaccount','DBA2','d
omain\administrator') and
ltrim(rtrim(left(program_name,8))) in ('MS SQLEM','SQL Query Analyzer')
--drop table #audittrace
select * from #audittrace
declare @.count int
declare @.message varchar(1000)
set @.count = (select count(*) from #audittrace)
While @.count >=1
begin
set @.message = (select 'SQL Security Enhanced Auditing: SPID =' + spid +'
,
Database: ' + [Database] +
' ,Loginame: ' + loginame + ' ,hostname: '+ hostname +' , Program
Name: ' +
program_name from #audittrace where traceid = @.count)
set @.count = @.count-1
RAISERROR (@.message, 16, 1) with log
end
drop table #audittrace
"philt" <philt@.discussions.microsoft.com> wrote in message
news:146F11E1-524A-4FC0-A6CC-45EC33206D15@.microsoft.com...
> SQL Server 2000
> How do I monitor who is adding or removing logins from a sql instance?
> and/or
> How do I monitor who is adding or removing logins from sql server roles?
> Thanks in advanced|||Phit,
Use SQL profiler and watch Security Audit and use the appropriate event type
Vinu
"philt" <philt@.discussions.microsoft.com> wrote in message
news:146F11E1-524A-4FC0-A6CC-45EC33206D15@.microsoft.com...
> SQL Server 2000
> How do I monitor who is adding or removing logins from a sql instance?
> and/or
> How do I monitor who is adding or removing logins from sql server roles?
> Thanks in advanced|||Thanks vinu, I'm using Profiler but I'd like to run this in the backgroud
like a service. I've created a template that I'd like to use when SQL server
starts up. I'm doing plenty of reading (SQL Bookis Online) but I'm not get
that far after creating the template and running via Profiler. Or if you or
others could point me to some good documentation/examples.
Thanks in advance.
"vinu" wrote:

> Phit,
> Use SQL profiler and watch Security Audit and use the appropriate event ty
pe
> Vinu
>
> "philt" <philt@.discussions.microsoft.com> wrote in message
> news:146F11E1-524A-4FC0-A6CC-45EC33206D15@.microsoft.com...
>
>|||what you need to do is to create a server side trace.
Following link might be helpfull..
http://vyaskn.tripod.com/server_sid..._sql_server.htm
this link will tell you how to setup a startup script
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
Vinu
"philt" <philt@.discussions.microsoft.com> wrote in message
news:4C5DDE2A-F5E1-43F5-98A2-1B00ACCE3542@.microsoft.com...[vbcol=seagreen]
> Thanks vinu, I'm using Profiler but I'd like to run this in the backgroud
> like a service. I've created a template that I'd like to use when SQL
> server
> starts up. I'm doing plenty of reading (SQL Bookis Online) but I'm not get
> that far after creating the template and running via Profiler. Or if you
> or
> others could point me to some good documentation/examples.
> Thanks in advance.
>
> "vinu" wrote:
>|||thanks for your advice vt,
I can't even get the sp_trace_create script to work. Have you used the
script before? or could you maybe do a quick test to see if the script works
for you?
Thanks again,
"vt" wrote:

> what you need to do is to create a server side trace.
>
> Following link might be helpfull..
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> this link will tell you how to setup a startup script
> http://www.microsoft.com/technet/se...r/sql2kaud.mspx
> Vinu
>
> "philt" <philt@.discussions.microsoft.com> wrote in message
> news:4C5DDE2A-F5E1-43F5-98A2-1B00ACCE3542@.microsoft.com...
>
>