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.
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.
ProjectChampion
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...
ASKER
@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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.