what is the REAL user? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

what is the REAL user?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-27 : 11:20:52

Will writes "Will writes "I have created a trigger for when a database gets updated, inserted, deleted, etc. One of the fields is the user that modified the record. I have tried USER, SESSION_USER, CURRENT_USER, etc and they always populate the field with dbo. Am I doing something wrong, or just not using the right thing.

Thanks,
Will Blackmon
Information Technology
Bank of Alabama""

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:35:08

Does your application establish database connections using one specific user or does it impersonate the domain credentials of the user it is acting on behalf of?

setBasedIsTheTruepath
<O>

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-28 : 04:17:53

The same kind of question was asked here - http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14151 - would be interesting to find out the answer...

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-28 : 07:55:36

From what my research shows, the real user is SYSTEM_USER. I had the same problem and basically tried all of them until I got the right answer. As far as getting the real user ID, I use the following:

SELECT USER_ID(SUBSTRING(system_user, CHARINDEX('\',system_USER)+1, LEN(system_USER)-CHARINDEX('\',system_USER)+1))

Not sure if this works in all cases. I will try to test and post the results.

Jeremy

I have just tested a little bit and it appears this works a little different in the production environment than in my test environment. Try the following as well to see which on produces the results and the REAL USER.

SELECT SUSER_ID(system_user)

Edited by - joldham on 03/28/2002 08:28:28

wblackmon
Starting Member

3 Posts

Posted - 2002-04-04 : 11:09:51

OK, I tried both:
USER_ID(SUBSTRING(system_user, CHARINDEX('\',system_USER)+1, LEN(system_USER)-CHARINDEX('\',system_USER)+1))

SUSER_ID(system_user)

And now I am getting '7' as the result.

Here is the code for the trigger that is producing '7' now:
CREATE TRIGGER [update_history] ON XXXXXXXX
FOR UPDATE
AS
INSERT XXXXXXXX_history (FirstName, MiddleInitial, LastName, Address1, Address2, City, State, PostalCode, Province, Country, InternationalYN, HomePhone, WorkPhone, SocialSecurityNumber, EmailName, DistNum, actnum, entered, loaded, ipadd, rmthost, pannumber, cardstatus, datechanged, action, modified)
SELECT FirstName, MiddleInitial, LastName, Address1, Address2, City, State, PostalCode, Province, Country, InternationalYN, HomePhone, WorkPhone, SocialSecurityNumber, EmailName, DistNum, actnum, entered, loaded, ipadd, rmthost, pannumber, cardstatus, GETDATE(), 'BEFORE UPDATE', USER_ID(SUBSTRING(system_user, CHARINDEX('\',system_USER)+1, LEN(system_USER)-CHARINDEX('\',system_USER)+1))
FROM deleted

INSERT XXXXXXXX_history (FirstName, MiddleInitial, LastName, Address1, Address2, City, State, PostalCode, Province, Country, InternationalYN, HomePhone, WorkPhone, SocialSecurityNumber, EmailName, DistNum, actnum, entered, loaded, ipadd, rmthost, pannumber, cardstatus, datechanged, action, modified)
SELECT FirstName, MiddleInitial, LastName, Address1, Address2, City, State, PostalCode, Province, Country, InternationalYN, HomePhone, WorkPhone, SocialSecurityNumber, EmailName, DistNum, actnum, entered, loaded, ipadd, rmthost, pannumber, cardstatus, GETDATE(), 'AFTER UPDATE', USER_ID(SUBSTRING(system_user, CHARINDEX('\',system_USER)+1, LEN(system_USER)-CHARINDEX('\',system_USER)+1))
FROM inserted

Hope this gives you a little bit better Idea of what is going on.

Will Blackmon
Information Technology
Bank of Alabama

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-04-04 : 13:38:46

Will,

See my post at the following URL.

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14524[/url]

I looked into this a little bit more and found a difference between NT Authenticated Users and SQL Authenticated Users. Also, the reason you have a 7 in the column is that the code I provided lists the user_id instead of the user name. At the above post, it shows how to obtain either user_id or user_name. You can get the user_name based on the user_id in your table by querying the sysusers table and linking to the id column.

If you have any questions after reading the post mentioned above, please let me know.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources