How to fetch User Profile using Trigger in MS SQL Server 2000

Question

Post reply

How to fetch User Profile using Trigger in MS SQL Server 2000

nishidhpatel19

SSC Rookie

Points: 38
More actions
April 7, 2009 at 1:16 pm

#73597

Initially, My application was installed as normal Windows Application. I have a trigger in my application to maintain the audit tables. For each Insert, Update and Delete operation, I was storing Computer Name from where data manipulation tasks had been performed. Trigger was working fine in that case.
Now, Application is published on Citrix Server. Now, trigger is only storing the Computer Name on which application is installed. Rather I need to store the User Name (User Profile Name under C:\Documents and Settings\Profile Name or User Name), who access the application through Citrix Session.
Any help is highly appreciated.
Thanks

Viewing 11 posts - 1 through 11 (of 11 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are various User_Name functions in SQL Server.

user_name()

system_user

Are two of them. Try those, see which one gives you what you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

If you are using Windows Authentication for your users you can suser_sname() to return the user name.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

This is nothing the functions mentioned above won't give you, but I have found these fields useful a couple of times.

SELECT

NT_Login = m.name,

UserName = s.name

FROM master..sysxlogins m,

sysusers s

WHERE s.sid = m.sid

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

nishidhpatel19 SSC Rookie Points: 38 More actions · Answer 4

Thanks for the reply.

As per my knowledge sysxlogins stores the SQL Server logins in master database. Whenever we create a new login (or grant access to a Windows account), a corresponding entry gets added to the sysxlogins table. so ultimately its SQL Server Login, through which we can access the database.

In my application, I want the User profile Name (windows login name) to be accessed in trigger.

I want to describe my scenario over there :

My Citrix Server Name is : CitrixServer01 - on which application resides

User connects to Citrix session through web interface using his/her Windows credentials : WinUserName & WinPwd

Access the application using : AppLogin & AppPwd

Inside Application, database is connected through connectionstring having : SQLLogin & SQLPwd

I want to keep track the audit trail for the data manipulation actions performed by which user.

So If I use "SUSER_SNAME()" or "System_User", I will get SQL Server Login Name which is SQLLogin in my scenario that I do not want to store in audit trail.

If I use "HOST_NAME()", I will get CitrixServer01 as Host Name, which is common for all user who access the application through Citrix.

In my case, I want to store the "WinUserName" in audit trail action, who actually connects to Citrix Session and performed the action.

Thanks for your co-operation.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Are you using an Application Role? If you are you may be able to do something to get the Windows user name based on the BOL entry for sp_setapprole.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sergiy SSC Guru Points: 110209 More actions · Answer 6

In my case, I want to store the "WinUserName" in audit trail action, who actually connects to Citrix Session and performed the action.

You answered your own question.

If you need details about connection to Citrix you should collect it there, in Citrix session.

_____________
Code for TallyGenerator

nishidhpatel19 SSC Rookie Points: 38 More actions · Answer 7

Thanks for the Reply.

That is what I wanted to ask.

How to fetch the WinUserName (User Profile Name on Citrix) through MS SQL Trigger?

Thanks

nishidhpatel19 SSC Rookie Points: 38 More actions · Answer 8

Thanks for reply.

I am using Database user role. Connecting the Application using the Database user Name & Pwd.

Is there any other way, we can fetch the Windows User Profile Name?

Thanks

nishidhpatel19 SSC Rookie Points: 38 More actions · Answer 9

Thanks for the Reply.

sysxlogins table is available to master database and user who has admin previleges.

I am using normal database users (non-admin users) to access the applications & its relevant tables. So sysxlogins table would not be available to non-admin users.

Thanks

nishidhpatel19 SSC Rookie Points: 38 More actions · Answer 10

Any help is highly appreciated. I am in urgent need of this solution.

Thanks in advance.

Nishidh