October 19, 2017 at 3:09 pm
I've got a colleague that went a little keyboard happy two weekends ago. He and his development team were attempting to deploy some code to our production SQL instance and ran into a number of permission issues.
In an attempt to troubleshoot they started firing off ALTER AUTHORIZATION commands on our main user database. Immediately after doing so, we started seeing multiple agent jobs fail, because the majority of the default permissions were revoked. We ended up declaring BCP and rolling back to the last good copy of the db.
My colleague is now in a defensive position - it's pretty clear when we look at the SQL logs from PROD, he was firing off commands that caused this behavior.
Here's the issue:
A couple days later - the same exact issue occurred again in our UAT environment (a mirror copy of PROD). This time though - there's no reference in the SQL logs of any attempt to run an ALTER AUTHORIZATION command.
Here's my question - if SA was being used in UAT to run the ALTER AUTHORIZATION commands - would that be sent to the SQL Logs? My assumption is that we don't see these statements because they were logging into UAT as SA - as opposed to what they did in production, which was under my collegues NT login.
Thoughts? What resources are out there to help explain this.
October 19, 2017 at 4:12 pm
ian.todd - Thursday, October 19, 2017 3:09 PMI've got a colleague that went a little keyboard happy two weekends ago. He and his development team were attempting to deploy some code to our production SQL instance and ran into a number of permission issues.In an attempt to troubleshoot they started firing off ALTER AUTHORIZATION commands on our main user database. Immediately after doing so, we started seeing multiple agent jobs fail, because the majority of the default permissions were revoked. We ended up declaring BCP and rolling back to the last good copy of the db.
My colleague is now in a defensive position - it's pretty clear when we look at the SQL logs from PROD, he was firing off commands that caused this behavior.
Here's the issue:
A couple days later - the same exact issue occurred again in our UAT environment (a mirror copy of PROD). This time though - there's no reference in the SQL logs of any attempt to run an ALTER AUTHORIZATION command.
Here's my question - if SA was being used in UAT to run the ALTER AUTHORIZATION commands - would that be sent to the SQL Logs? My assumption is that we don't see these statements because they were logging into UAT as SA - as opposed to what they did in production, which was under my bosses NT login.
Thoughts? What resources are out there to help explain this.
If you have the default trace enabled, it picks up GDR events so you should be able to find it in those logs if you still have those available.
Sue
October 19, 2017 at 6:56 pm
hmm... default traces are the same on both setups (prd vs. uat) when i use following
SELECT * FROM :: fn_trace_getinfo(default)
If the trace settings are the same, how is it possible this was only logged when this issue occurred in prod, why didn't it show up in my UAT logs.
makes me doubt my base theory... but what else could've caused the behavior? my prod and uat environments are segregated!
October 20, 2017 at 8:22 am
ian.todd - Thursday, October 19, 2017 6:56 PMhmm... default traces are the same on both setups (prd vs. uat) when i use following
SELECT * FROM :: fn_trace_getinfo(default)If the trace settings are the same, how is it possible this was only logged when this issue occurred in prod, why didn't it show up in my UAT logs.
makes me doubt my base theory... but what else could've caused the behavior? my prod and uat environments are segregated!
The results from that query will give you the trace settings, file location but doesn't provide what is actually in the trace file.
The trace file itself is what you would want to look at from whenever this took place if that file is available. You'd want to find the trace that would have been running during the time of the activity you are looking for. At a high level to get the information from a specific file and filter that on just Audit events, you could use something like: SELECT
te.name,
tr.*
FROM fn_trace_gettable('X:\YourPathToTheTraceFile.trc', 1) tr
INNER JOIN sys.trace_events te
ON tr.eventclass = te.trace_event_id
WHERE te.name like 'Audit%'
Sue
October 20, 2017 at 11:31 am
got it. looks like my window has passed because I can only see *.trc files from the past 24 hours.
appreciate all the help!
October 20, 2017 at 11:39 am
ian.todd - Friday, October 20, 2017 11:31 AMgot it. looks like my window has passed because I can only see *.trc files from the past 24 hours.appreciate all the help!
Dang...sorry you missed that window. But at least now you know what to go check. If things like that happen regularly or you suspect it could be a problem in the future, you might want to setup your own auditing of some of those. The trace files are generally the only way to catch things like that after the fact unless you have some of your own auditing in place.
Sue
October 20, 2017 at 1:56 pm
You can also set up an Event Session. In SSMS 2014, there is a function for this (before, you had to work your way through the scripting). You can set custom retention periods, and also limit the output with almost any filter. You can get all commands issued by SA, any GDR event, and command that contains certain text (such as Authorization). Then to retreive the data, you use the function sys.fn_xe_file_target_read_file. Here is a link to get you started with Event Sessions:
It takes a bit to get used to it but it is well worth it.
Oh, and revoke any sysadmin permissions the developer group(s) have. This is a crisis that should not go to waste.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply