October 4, 2010 at 3:04 pm
Dant...
Dynamic SQL isn't just what's pumped in from a website. It's what's typed into SSMS, ran from a job and not proc called, etc. EXEC sp_yay @blah is a dynamic call, actually, when typed into SSMS. I should have been more specific there.
Regarding the snooping, if you can setup a snoop on a single database, you can snoop anything. It requires server level permissions to fire up a trace. Guess what your login *isn't* going to have unless you're a vendor app I'm setting up on a throwaway server that's security isolated from my network... at least if the DBA is competent.
You mention you'll have sa. Run the trace. Take the files. Upload to a table and strip 'em down. You have the contract and license on your side and you mentioned being sa. Or have business sign off on a lack of enforcability and move on. 🙂
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
October 4, 2010 at 3:04 pm
I was hoping I would be able to pull something with event notification but I can only create it with server-wide scope, which is a no-go. I'll study further the local trace to a file option.
Thanks everybody who helped
--
Thiago Dantas
@DantHimself
October 5, 2010 at 12:06 pm
Looking at the options...
Triggers won't work because they won't capture SELECT statements.
Trace will work, but the DBA can easily stop it. Also, how will you get the data back into something queryable? Also, will you have the proper permissions to be able to successfully execute a trace? How will you ensure it runs when SQL Server is restarted?
Events are server-side, and again, can be stopped.
Audit (if they're running SQL Server 2008 Enterprise) will pick up the SELECT statements and can be at the database level (at least the specification can be), but the Audit object itself is at the server level. And can be stopped.
Michael is right here. You're trying to solve a business problem with a technical solution. And short of a third party product they'd have to install on their server, anything you do they'll be able to undo pretty quickly. I'm not intentionally trying to be negative here, but that's the reality of the situation when they hold sysadmin rights over their SQL Server.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply