Getting the user log in name 2

[xxing]

Hi

My Manager wants a report that limits the records to a user.
So I would have the where clause of my query have the following:

where person like '%' + @LoginName + '%'

@loginName contains the username of the person who logged into windows. The AD username.

Is there a function that gives me the username?

I use SQL Server 2005. The function should work in a stored proc called by reporting services

Thanks for your help

Regards
Mark

 

[EKOnerhime]

If you need the currently logged in user including the domain name, SYSTEM_USER will give you this information.

As long as the stored proc used by SSRS is being executed using the currently logged in users credentials, all should be well.

Our current SSRS reports pass this information as a parameter so it's an educated guess.

 

[monkeymagic2222]

Is it possible to do this within a SQL Trigger statement? If I run a straight query using SELECT SYSTEM_USER I get my AD logon which is fine, however if I use this same statement in my trigger I get the database logon (dbo). Is there some way to pass the AD username to the trigger statement?

Thanks,

Simon

 

[jrbarnett]

SELECT SUSER_SNAME()

John

 

[monkeymagic2222]

John,

I should have mentioned before that I've also tried SELECT SUSER_SNAME() and I get exactly the same issue. Returns my Active Directory logon in a standard query but the database logon in a trigger statement. I realise that this is what should be happening as the trigger is fired by the database and not the user but I wonder if there's a way to pass the users logon into the trigger?

Thanks,

Simon

 

[xxing]

Thanks for your help

 

[jrbarnett]

The only way to do that is to pass it in as a parameter (ie a column in the table being updated/inserted) then you can read it from the inserted table.

John

 

[TysonLPrice]

I saw this in another post. Maybe it can be tweaked to get the username inside a trigger for your purposes:

create table Logins
(-- Your system's user ID
userName varchar(50),
-- SQL server process ID
spid integer)

when your program starts write this info into this table.

insert into logins(username,spid) values(SUSER_SNAME(),@@SPID)

when the trigger fires, it knows the current sid from the @@spid, and gets the user id from this table.

Heres where I saw it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=447959&SiteID=17