Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scholes
Starting Member
4 Posts |
 Posted - 2005-10-13 : 10:18:22
|
| I have a client server application running,the client machine connects to SQL server 2000 on the server via mixed mode authentication. So the user at the client machine, runs the client part of the application, which then connects to the server. Now, a table in the database has a trigger which fires when an update is done to this table. this trigger records information of who(made the change) and what was changed in the table. What i want do is to to get the name of the user who has logged into the client machine. The users log in with their domain usernames eg: S2\Fayyaz.Asmalnow in my trigger, if i use SYSTEM_USER or any of the other functions(USER_ID etc etc), then i do not get the name of the user logged in at the client, instead i get NT AUTHORITY\SYSTEM. But i want the domain user name of person logged in. CAN ANYBODY HELP ME? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-13 : 10:58:49
|
| Hi scholes, Welcome to SQL Team!What's the client application logging in to SQL Server as? if its using a SQL Server login, rather than a Windows/NT Authentication login, then I don't reckon you will be able to get the UserIDSUSER_SNAME() might be more appropriateI reckon your choices are:SELECT USER_NAME, SYSTEM_USER, SUSER_SNAME()Kristen |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-13 : 14:22:59
|
| Yep, I'm working on a similar challenge here. If you connect via SQL Login instead of a NT Login, the server doesn't know or care who you are on the client. It only knows who you claimed to be when you logged in to SQL. There are only a couple of options I have found:1) Switch to an NT Login2) Pass in the client's user ID as a parameter (works for sprocs, but triggers don't take parameters).3) Modify the connection string to include the client's user ID as the WORKSTATION information which can then be retrieved using HOST_NAME(). WARNING! This option has implications for connection pooling and performance.---------------------------EmeraldCityDomains.com |
|
|
|
scholes
Starting Member
4 Posts |
Posted - 2005-10-14 : 03:42:46
|
| Thanks guys for your comments so far. What do you know of program_name in sql. It returns a long string, which includes the process id, process name, and also the domain user of the client, but it cuts out part of the username eg: mark.john where the names supposed to be mark.johnsoni will try your suggestions and get back to you. |
|
|
|
scholes
Starting Member
4 Posts |
Posted - 2005-10-14 : 04:09:03
|
| Sorry, forgot to mention that this Application that is running on the client-server is a 3rd party app. we do not have the code. It uses a System DSN odbc connection which uses NT authentication(using network login id) to connect to SQL database.now why am i still not getting the Login detail of client?? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-14 : 07:17:14
|
| "now why am i still not getting the Login detail of client"Dunno, but I reckon you should be!Can you run some simple SQL Client tool on a client PC, logged in via the client's NT Authentication, and runSELECT USER_NAME, SYSTEM_USER, SUSER_SNAME()and see what you get?Kristen |
|
|
|
|
|
|
|
|
Get domain login of user on client machine