How can I make my SQL statements not to appear in Profiler ? They contain sensitive information and I don't want them to show in Profiler.
Thanks for the replies !
+2
A:
The profiler can only be run by someone with proper rights so if your installation is properly secured you should be fine (no users should be able to profile your application).This goes beyond security, since profiler slows down the server considerably.
Otávio Décio
2009-12-01 03:54:18
This is not always the case. The scenario where a DBA may have administration rights to the database (for maintenance), however have no right to view sensitive details inside the database. Encryption/hashing the information is one solution to provide for both scenarios.
Russell
2009-12-01 04:39:42
+2
A:
Only way I can think of to do this would be to make them stored procedures (The profiler would only show the call), but if the arguments are what's secure (which is likely) then that won't help you (Edit: As pointed out in comments you can change the config of the profiler to include this anyway, so this won't help much)
Have you considered not giving access to people not allowed to view the data? Access to run the profiler is a pretty high level of access...
Also, have you considered hashing your data before storing/querying it? Sometimes that won't work, but if we're talking passwords then they really should be stored and looked up in an encrypted form anyway.
Tim Schneider
2009-12-01 03:56:31
Making them stored procedure is not enough since the SP:StmtCompleted event can still be used to trace stored procedures execution (http://msdn.microsoft.com/en-us/library/ms189570.aspx)
Remus Rusanu
2009-12-01 04:57:09
But that won't show the SQL within the actual stored procedure. As I said it'd only work if it was the SQL itself and not the arguments you needed to protect. (Though in hindsight anyone who has access to run a profiler trace probably has enough to show the definition of a stored procedure)
Tim Schneider
2009-12-01 05:37:23
No, that **will** show the actual statement being executed, step by step.
Remus Rusanu
2009-12-01 06:40:19
+1
A:
There is no way. The "text" column in profiler can not be removed from use.
You need rights to run profiler (sysadmin or GRANT ALTER TRACE) so it's not an implicit right.
Note:
- sysadmins could decrypt stored procs or add logging code, regardless of whether they run profiler
- physical access needs controlled, at least to stop someone taking a copy of the database away
- things like sp_password or ALTER LOGIN are not traced anyway
gbn
2009-12-01 04:35:19