May 11, 2010 at 3:39 pm
So here is a wonderful thought. What if all of your developers knew the SA password and used it on production to do most everything? That is what we just got done dealing with.
After almost 1 year, we have gotten most things off of SA to use actual logins. What I need help with is this.
I currently have a trigger that records the IP, Host_name, and App_Name for who ever logs in with SA. I would also like to be able to store off the sql statments that go with the account.
How would I do this from a trigger stand point or does it all need to be done via trace?
Thanks,
Fraggle.
May 11, 2010 at 4:18 pm
Doing it from a trigger standpoint is a bit tricky. You would need to be able to capture instantaneously what the person ran. You could include code to record the sql text of what has been executed in the trigger - it just may not be highly successful due to timing issues. The higher transaction volume you experience, the easier it would be to trap the text of the sql being executed by that person.
An alternative is to use a job that captures the user and sql commands every 10 seconds or so. Again, you may not capture everything due to timing.
The last alternative is to use a trace.
That is if you want to capture the data from when they logon as SA.
The ultimate alternative is to change the sa password and see if anybody complains and if anything breaks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 7:53 am
The final goal is to change the password so I can, but I got a funny feeling there is still stuff out there that is relying on the SA password. So I am trying to find it.
I was hoping there was something else other than a trace, but it would appear not. Thanks for the assistance.
Nathan
May 12, 2010 at 10:59 am
Your welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 11:13 am
Changing the SA password will help find those things..
In my environment we set the SA password to some value that is generated randomly and never displayed, so no one knows what the password is, not even the DBAs. We then create an another user like sa that we really never use. We do this because SA is a well known user and the other is not. WE can also change the SA user at will.
As far as tracing you could setup a trace that ONLY tracks logins, that should be a fairly light trace in terms of performance hit. I doubt the system would even notice.
I have a system using LOGON triggers where I track ALL logons, this is used for auditing and troubleshooting.
CEWII
May 12, 2010 at 11:21 am
Server-side profiler trace is your best option to get all the data you want and not miss any logins.
Make sure to include the TextData field to get the procs and other code.
May 13, 2010 at 2:56 am
If you are running SQL2K8 why not look at the SQL Server Audit? Not sure if this would met all your requirements but I'd be reluctant to use traces....
May 13, 2010 at 8:25 am
Audit is only available on Enterprise edition.. I agree, you need to be very careful with traces or your server can take a big hit.
CEWII
May 15, 2010 at 12:20 am
Hi,
One more thing to add here is; running the SQL Server Profiler from another machine (not the server itself) will help in minimizing the performance impact.
Good luck! 😉
May 15, 2010 at 8:55 am
Hossam AlFraih (5/15/2010)
Hi,One more thing to add here is; running the SQL Server Profiler from another machine (not the server itself) will help in minimizing the performance impact.
Good luck! 😉
Be careful, I have seen it still bring the production server to its knees.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 15, 2010 at 10:29 am
I'd also trace to a file, set permissions on the folder for only someone auditing and the service account to write there, copy the files off to another server each night.
I think you want to capture minimal information here, time, db, command, and then backtrack things from there so that you have a minimal trace. You can set the trace to only work with certain logins and I would agree that you want to change the sa pwd to prevent someone from making anonymous changes.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply