April 1, 2015 at 3:02 am
Dear All,
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances. Please let me know your inputs regarding this. Thank you.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
April 1, 2015 at 3:38 am
Look at SQL Audit, extended events or SQLTrace.
That said, it's trivial for the program name to be spoofed on connection, so what the connection says it's connecting from may not actually be what it really is connecting from
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
April 1, 2015 at 3:46 am
Thanks Gail. Actually, the below site helped to get the connection source..
http://thesqldude.com/2012/04/07/how-to-prevent-users-from-accessing-sql-server-from-any-application-or-any-login-expect-your-main-application-its-login/[/url]
However I will look at SQL Audit as you mentioned as I feel it will help in this case.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
April 1, 2015 at 3:51 am
karthik babu (4/1/2015)
Actually, the below site helped to get the connection source..
*sigh*
The application name passed to SQL can trivially be spoofed, so the app which the connection says it's coming from is not necessarily the app which the connection is really coming from.
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
April 1, 2015 at 4:02 am
Oh k..Thanks again, Gail. Can you please elaborate it or show some proof... Just to make myself clear.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
April 1, 2015 at 4:13 am
Open up Management Stdio, open the login dialog for a server, click the options button and then the 'additional connection parameters' tab and look at the free-text box there that lets you specify any aspect of the connection string you like, including the host name, the application 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
April 1, 2015 at 4:24 am
Oh.. yeah.. Its interesting and didn't know until now. Thanks for the information, interesting though.
So what's the best way to achieve my goal?
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
April 1, 2015 at 4:30 am
SQL Audit, SQL Trace or extended events and hope your users/developers also don't know about that trick and keep in mind that someone determined may be able to avoid your auditing. Or put in IT policies that make such spoofing a dismissible offense.
SQL only knows what comes to it, it can't tell if the client app has overridden the app name as it's a perfectly legit use of the connection string.
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
April 1, 2015 at 6:47 am
The SQL Audit worked just perfect. But couldn't create audit for more than one table though it allows you to select more tables. Can we select multiple tables in a single audit? If no, why is it allowing to choose two or more objects? Please clarify.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply