May 25, 2012 at 10:57 am
Hello everyone,
Is there a way to capture the username to who is currently log in the application when a delete trigger has been fired? I want to know because I want to capture the username who deleted the data. Thanks!
May 25, 2012 at 11:14 am
SELECT SYSTEM_USER
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 11:15 am
SELECT Original_Login()
Just in case there's any impersonation going on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2012 at 1:25 pm
Is CDC a new feature of SQL server 2008 be useful for such a capture ? Or change tracking ?
June 18, 2012 at 2:51 am
I think I did not stated clearly my post and I'm sorry for that.
The issue is... A user logon into a machine using domain account ("domain_account"). Then the user logon into the application search and delete the data. The application account is "application_account". I want to capture the domain account which is domain_account NOT the application account so that I would know who deleted the data.
Thanks in advance for your response.
June 18, 2012 at 3:08 am
Does the application pass anything to SQL Server to identify the original user? (login name, host name, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 4:42 am
Nothing except the ID(primary key) of the data.
I think I would need to pass the username and use the output clause. Would you think that make sense?. I'll try it now 🙂
June 18, 2012 at 4:58 am
eklavu (6/18/2012)
I think I would need to pass the username and use the output clause. Would you think that make sense?. I'll try it now 🙂
Doesn't work that way. Only columns in the table can be outputted
I meant as part of the connection string (host name or something). If the app doesn't tell SQL who is connecting to it, SQL cannot know.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 6:11 am
Unfortunately I dont think this would be possible - due to "Applications" using a generic "SQL Login" for transactions.
The user level security is handled by the application interface and therefore only allows the authorised transactions through to SQL - which would then use the SQL details to process the transaction.
You may need to look back a step and have some audit checking within the application itself ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 18, 2012 at 4:42 pm
If you run
select * from sys.sysprocesses WHERE spid = @@SPID
does it give you what you need to know in the nt_domain and nt_username columns? I'm running this on SQL 2012 and have no SQL2K8 instance available at the moment, so I'm not sure whether this will work for you.
Regards,
Jan
June 18, 2012 at 4:45 pm
Jan Van der Eecken (6/18/2012)
If you run
select * from sys.sysprocesses WHERE spid = @@SPIDdoes it give you what you need to know in the nt_domain and nt_username columns? I'm running this on SQL 2012 and have no SQL2K8 instance available at the moment, so I'm not sure whether this will work for you.
Regards,
Jan
Jan, the problem is it's an impersonation login from the App. Windows or SQL Login, it all comes in as the app's login. Really the only way I know to do this is to track the IP the connection's coming from, and that can get goofy if you're middle tiered via IIS or N-Tier objects.
In general... no, not that I'm aware of, not for an impersonated app login. I'll be very happy to be proved wrong though, this has been a thorn in my side for years.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 18, 2012 at 5:18 pm
Evil Kraig F (6/18/2012)
Jan Van der Eecken (6/18/2012)
If you run
select * from sys.sysprocesses WHERE spid = @@SPIDdoes it give you what you need to know in the nt_domain and nt_username columns? I'm running this on SQL 2012 and have no SQL2K8 instance available at the moment, so I'm not sure whether this will work for you.
Regards,
Jan
Jan, the problem is it's an impersonation login from the App. Windows or SQL Login, it all comes in as the app's login. Really the only way I know to do this is to track the IP the connection's coming from, and that can get goofy if you're middle tiered via IIS or N-Tier objects.
In general... no, not that I'm aware of, not for an impersonated app login. I'll be very happy to be proved wrong though, this has been a thorn in my side for years.
Evil :w00t: Craig, you are right. Didn't quite get that impersonation issue. Then again, I've never played with App roles before. Yeah, you are right, trying to figure out what the IP is where the request came from, and then possibly tying it back to AD might be a possible solution. Or maybe not?
June 18, 2012 at 5:20 pm
June 18, 2012 at 5:26 pm
Jan Van der Eecken (6/18/2012)
Oops, I misspelled your name. Apologies, Kraig!
LOL, at this point, either is acceptable. 😉 :w00t:
It just depends on which of the twins you want to talk to... the evil one with his goatee or his occassionally shaved good brother. 😎
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 18, 2012 at 5:36 pm
Evil Kraig F (6/18/2012)
Jan Van der Eecken (6/18/2012)
Oops, I misspelled your name. Apologies, Kraig!LOL, at this point, either is acceptable. 😉 :w00t:
It just depends on which of the twins you want to talk to... the evil one with his goatee or his occassionally shaved good brother. 😎
Now which one of the two is you? I wouldn't mind shaving either.
Got twins too, younger ones. One of them certainly doesn't need shaving. 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply