October 4, 2010 at 12:49 pm
What would be the easiest way to audit usage of a single database by a specific user, without creating a server-wide trigger?
Assume sql server 2008 enterprise
--
Thiago Dantas
@DantHimself
October 4, 2010 at 12:52 pm
dant12 (10/4/2010)
What would be the easiest way to audit usage of a single database by a specific user, without creating a server-wide trigger?Assume sql server 2008 enterprise
Easiest? Run a profiler trace with the filters set to the login and databasename with the things you want to scan for.
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 1:09 pm
Craig Farrell (10/4/2010)
dant12 (10/4/2010)
What would be the easiest way to audit usage of a single database by a specific user, without creating a server-wide trigger?Assume sql server 2008 enterprise
Easiest? Run a profiler trace with the filters set to the login and databasename with the things you want to scan for.
Unfortunately, I can't start a trace through the SPs to save the trace on table. And I can't leave profiler running. It has to be background and as invisible as possible.
Ok, maybe not the easiest :unsure:
--
Thiago Dantas
@DantHimself
October 4, 2010 at 1:11 pm
dant12 (10/4/2010)
Unfortunately, I can't start a trace through the SPs to save the trace on table. And I can't leave profiler running. It has to be background and as invisible as possible.Ok, maybe not the easiest :unsure:
Help me out, what are you trying to do here? Specifically. That bolded section leaves a lot to be implied in a few directions.
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 1:15 pm
Why can't you save the results to a table, out of curiosity?
Would saving it to a file be a better option?
Literally your only option here is to run a profiler trace..and this is what it was designed for.
October 4, 2010 at 1:29 pm
Derrick Smith (10/4/2010)
Why can't you save the results to a table, out of curiosity?Would saving it to a file be a better option?
Literally your only option here is to run a profiler trace..and this is what it was designed for.
I want to save to a table, I can't because the SP_TRACE_CREATE doesn't let me
Our application database is usually managed by us.
This new client doesn't want us managing the database and we don't want him running any selects around. It is being put on the contract but I will need to put in place this sort of monitoring on our database to enforce it.
--
Thiago Dantas
@DantHimself
October 4, 2010 at 1:46 pm
dant12 (10/4/2010)
Our application database is usually managed by us.
This new client doesn't want us managing the database and we don't want him running any selects around. It is being put on the contract but I will need to put in place this sort of monitoring on our database to enforce it.
So you're trying to do a hidden trace on a system that's not owned by you, but you've designed and licensed to the client. Do I understand that correctly?
"Running any selects around" means... what exactly? You don't want them doing dynamic SQL or you don't want them doing anything but working directly with your procs? Once you've lost control of this database, anyone with sa can see, edit, and change anything they want to. You cannot, repeat, CANNOT guarantee anything on a database that you cannot control server permissions on.
That said:
Option one: Ship the .trc files to a location on the server with maxfilesize set. Create an automated job that polls for files that are not the 'last' (ie: foreach container on files) and ship them to youself from the foreign server. Or just leave them there and have another system come in and pickup. Either way, if you want this invisible, you need to be able to remove the data files or you'll bloat a disk. This would occur with a table, as well, it's just as hard to do however.
Option two: CREATE EVENT NOTIFICATION
You have to understand service broker for this, but it's very effective, depending on what you're looking for. The event SQL_STMTRECOMPILE will help give away dynamic SQL calls.
Option three: Startup parameters and audits. If you don't have administrative control of the server, don't go here.
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 1:56 pm
Craig Farrell (10/4/2010)
dant12 (10/4/2010)
Our application database is usually managed by us.
This new client doesn't want us managing the database and we don't want him running any selects around. It is being put on the contract but I will need to put in place this sort of monitoring on our database to enforce it.
So you're trying to do a hidden trace on a system that's not owned by you, but you've designed and licensed to the client. Do I understand that correctly?
"Running any selects around" means... what exactly? You don't want them doing dynamic SQL or you don't want them doing anything but working directly with your procs? Once you've lost control of this database, anyone with sa can see, edit, and change anything they want to. You cannot, repeat, CANNOT guarantee anything on a database that you cannot control server permissions on.
That said:
Option one: Ship the .trc files to a location on the server with maxfilesize set. Create an automated job that polls for files that are not the 'last' (ie: foreach container on files) and ship them to youself from the foreign server. Or just leave them there and have another system come in and pickup. Either way, if you want this invisible, you need to be able to remove the data files or you'll bloat a disk. This would occur with a table, as well, it's just as hard to do however.
Option two: CREATE EVENT NOTIFICATION
You have to understand service broker for this, but it's very effective, depending on what you're looking for. The event SQL_STMTRECOMPILE will help give away dynamic SQL calls.
Option three: Startup parameters and audits. If you don't have administrative control of the server, don't go here.
My problem is not dynamic sql, but ANY sql.
I know it will be pain to enforce this kind of thing, specially since I'm now sa on the server, but it needs to be done neverthless.
The company makes some profits from developing new reports based on clients requests, and that would go hell if they can run selects on the database. They will be forbidden to do so by contract but there must be something in place to monitor this.
I'm working with service broker on a couple other projects and i'll take a look in event notification, had no idea it could be used to something like this.
Thanks a ton
--
Thiago Dantas
@DantHimself
October 4, 2010 at 1:56 pm
dant12 (10/4/2010)
I want to save to a table, I can't because the SP_TRACE_CREATE doesn't let me
No, you absolutely do not. Not unless you want to cripple performance. Tracing to a table (fortunately only available via the profiler front end) is the absolutely worst way to trace a server. Most overhead, most impact.
Have you investigated SQLAudit? (since you're on 2008) I don't know offhand if it audits selects. If not, a trace is the only option left.
While you're at it, check that your contract allows you to snoop on your client's server, data and activities. To say it's borderline ethical is saying the very least. Also, any admin worth a damn will notice a server-side trace running. Likely when it runs a drive out of space.
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
October 4, 2010 at 2:01 pm
GilaMonster (10/4/2010)
dant12 (10/4/2010)
I want to save to a table, I can't because the SP_TRACE_CREATE doesn't let me
No, you absolutely do not. Not unless you want to cripple performance. Tracing to a table (fortunately only available via the profiler front end) is the absolutely worst way to trace a server. Most overhead, most impact.
Have you investigated SQLAudit? (since you're on 2008) I don't know offhand if it audits selects. If not, a trace is the only option left.
While you're at it, check that your contract allows you to snoop on your client's server, data and activities.
My first target was SQLAudit, but I don't think I can pick up select statements with that. And I don't intend to snoop his server activities, just the activities on a single database.
--
Thiago Dantas
@DantHimself
October 4, 2010 at 2:08 pm
It sounds like you are seeking a technical solution for what is really a business problem: your desire to continue a monopoly on report development.
If your company cannot continue to make a profit without this monopoly, then you need to look at what you are charging them and adjust the price.
If you are running a trace without their permission purely for this reason, then I would call that an unethical activity.
October 4, 2010 at 2:09 pm
Tracing to a file sounds like the best option then.
I take it there's no way for you to just explicitly deny select on any tables you don't want them accessing?
October 4, 2010 at 2:17 pm
Derrick Smith (10/4/2010)
Tracing to a file sounds like the best option then.I take it there's no way for you to just explicitly deny select on any tables you don't want them accessing?
No since they will have SA
@michael-2, it is not just that, we had problems in the past with things like this and we prefer to be prepared now.
--
Thiago Dantas
@DantHimself
October 4, 2010 at 2:21 pm
The only way I have ever seen successful auditing on SELECTs was by forcing them through stored procedures, and the stored procedure always logged its running as the first step of execution. Short of a trace, there is no way at all to trace the execution of a SELECT, not even a trigger (which only fires on DML events). If the client gets SA, you really have no way to prevent them from doing whatever they want.
October 4, 2010 at 2:23 pm
If they have the SA password, what's to stop them from just restoring a backup to another name or on another server and having all the info there? Or creating another login and selecting with that instead? You really can't control someone who has sysadmin..and anyone who knows anything about sql would be able to do either of those things (and seeing how you're handing the server off to them, I assume they have someone who does).
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply