Getting Windows User ID in Trigger or SP

Question

Getting Windows User ID in Trigger or SP

craigt

SSC Enthusiast

Points: 181
More actions
March 18, 2003 at 11:07 am

#101409

Hello,
I am developing a SQL Server application and am trying to accomplish something that I could do in Oracle but cannot seem to find an equivalent solution in SQL Server 2000.
The Problem:
I am trying to determine the ID of the User that is logged into the workstation that is attaching to a SQL Server Database on another Server. For example, John Doe logs into his windows 2000 workstation with the user id “jdoe”. John Doe then runs an application that connects to a SQL Server Database that exists on another machine. John Doe does not connect to the database with his windows login ID but with a generic SQL Server ID such as “sa”. Is there anyway to get the windows login id from the workstation that John Doe is working from inside a trigger or stored procedure?

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

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

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

Here are all the functions that might be of any use in this case. I do not think those will help to accomplish your goal. You ned to capture the user identity outside of the SQL Server if you are using the standard security option.

select USER

select USER_NAME()

select SYSTEM_USER

select SESSION_USER

select CURRENT_USER

select HOST_NAME()

bp SSCarpal Tunnel Points: 4656 More actions · Answer 2

just an aside note, you might want to review what kinds of permission the "generic SQL Server ID" sa should be having. In general, it is a good idea to give permissions to logins only as required. For example, for a user who only needs to read and write data, there is no need for that same user to able to change the database security settings.

Billy

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

As mromm said, SQL Server is only concerned with what login account was used to gain access to SQL Server, therefore you can only see that he is logged in as sa (or whatever). If he would use Integrated Security you would see his Windows account (DOMAIN\user).

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

HendraEP Mr or Mrs. 500 Points: 515 More actions · Answer 4

There is a difference between user concept in Oracle and SQL Server. In Oracle, you create a user on specific tablespace, then grant that user some privilege. In SQL Server, you create a login to allow someone to access database server. In addition you have to create a user that mapping the previous login on a specific database, which you give an access to that login.

One login could associated with some users in more than one database, which also can have the different name.

To get the login name, use SYSTEM_USER function.

Then look for the ID using sp_helplogins system stored procedure.

To get the user name , use USER_NAME function. To get the user ID, use function USER_ID

Hendra