We help IT Professionals succeed at work.

get windows user from t-sql

Hi experts,

What I need is simple (only to say and explain)
There are some users on my network, using a non-auditing-application, so I was wondering if there is a way, at least, to know the windows user logged in from where the query was called.

For example: WORKSTATION1 - IP:192.168.0.22 - DOMAIN\User22- EXEC PROC spr_test

If there is a way to do that I will be able to create some triggers to audit some of the critical actions within the business rule of the application.

Thanks in advance.

Regards,

Leandro Nuñez.
Comment
Watch Question

It's very likely that the application you've mentioned is using an application role for connection to the DB. If that's the case, then there's usually no way to identify individual users, because application roles contain no users. All the permissions are assigned to the app role , and the application roles are designed to let an App role take over the job of authenticating users from SQL Server. When the app activates an app role by supplying the PW and from that point onward, anyone using the application gets the permissions assigned to the app role. The auditing, if needed, should be done by the app and at the point logging into the app and later on every time a DB operation needs to be done...

Author

Commented:
@ProjectChampion: Thanks for your answer.

The app is connecting via ODBC to the SQL Database.
I'm able to identify the machine from where the connection is being made (like in SP_WHO2), but I was hoping to obtain more data from that pc in that instant, like the windows logged user...


Maybe with xp_cmdshell but I do not know the command to do that...
Hi leandronn,
if course you can see the computer from which the connection has been made, but unless the app itsel store additional info about the users and their activities, I'm afraid that's all you'll be able to get. Thu is in fct the whole point of App logins in SQL server so you don't have to worry about authentication and permissions for every individual user who access the DB through the pertinent app. SQL Server gives access and all the necessary permissions to the pertinent securable objects to the app (using the app login) and from that point onward it's responsibility of the app to authenticate and manage access to resources and methods to its users. If what you want to achieve is only auditing I'd collect the computer id and time of activity, which is better than nothing, but if you actually want to override user managment of the app that would affect all users cause from where you are (outside the app) you won't be able to distinguish individual users. Hope that helps.