How do I get the windows login used by a database user to connect to SQL Server

Question

Post reply

How do I get the windows login used by a database user to connect to SQL Server

nmccormick

Say Hey Kid

Points: 700
More actions
December 22, 2011 at 3:23 pm

#100804

I am using SQL Server 2008 R2 on Windows Server 2003.
We have 3 Active Directory groups, DivisionEast, DivisionWest, DivisionSupv. Windows user tuser1 is a member of DivisionEast and DivisionSupv. Windows user tuser2 is a member of DivisionWest.
In SQL Server we have defined a Windows authenticated login for each of the Active Directory Groups and a database user for each login.
For auditing purposes I would like to know the Windows authenticated login a database user used to connect to SQL Server. The procedure sp_who ans sp_who2 returns tuser1 and tuser2 in the loginame. Every query I've found so far returns tuser1 or tuser2, not DivisionEast, DivisionWest or DivisionSupv.
Can this be done? If so can you give me some guidance in what tables or procedures to use?
Thanks,
Nancy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

salum SSCommitted Points: 1598 More actions · Answer 1

I don't think that you can find this AD group details in SQl server.

java[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

see if either of these extended stored procedure examples help:

EXEC master..xp_logininfo

@acctname = 'mydomain\lowell',

@option = 'all' -- Show all paths a user gets his auth from

go

EXEC master..xp_logininfo

@acctname = 'mydomain\authenticatedusers',

@option = 'members' -- show group members

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nmccormick Say Hey Kid Points: 700 More actions · Answer 3

I believe this is what I need. I can use the loginame from SP_WHO and feed it to xp_logininfo to see the AD Group the user was authenticated by. Here's the code I created. And since we a name the database users the same as the Windows group I should be able to expand this to provide a listing of database permissions.

Thanks!!!

Nancy

DECLARE @i int

DECLARE @rowcount int

DECLARE @acct varchar(100)

DECLARE @whoisloggedon TABLE

(spid int,

ecid int,

status varchar(100),

loginame varchar(100),

hostname varchar(100),

blk int,

dbname varchar(100),

cmd varchar(100),

request_id int)

DECLARE @t1 TABLE

(seq int identity (1,1),

loginame varchar(100))

DECLARE @t2 TABLE

(accountname varchar(100),

type varchar(100),

privilege varchar(100),

mappedloginname varchar(100),

permissionpath varchar(100))

insert into @whoisloggedon

EXEC sp_who;

insert into @t1 (loginame)

select distinct loginame

from @whoisloggedon

where loginame != 'sa'

--select * from @whoisloggedon

--select * from @t1

set @i = 1

set @rowcount = (select COUNT(*)

from @t1)

--select @i, @rowcount

while @i <= @rowcount

begin

set @acct = (select loginame

from @t1

where seq = @i)

--select @acct

insert into @t2

exec master..xp_logininfo @acctname = @acct, @option = 'all'

set @i = @i + 1

end

select *

from @t2

GO