Consider this scenario.
You have a user called Bob who thinks today is a good day to become a sysadmin.
He runs the following command to escalate his privileges:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [bob];
Thankfully this fails and world domination will need to wait another day.
Now as a DBA you’ll be thinking, this activity will be all over the SQL ERRORLOG and/or Windows Event logs so you can have a chat with Bob about his intensions, just as you would with login failures.
Allais, there is nothing recorded in either of these.
Fortunately, the Default Trace does capture this event and you can have that chat with Bob after all.
SELECT StartTime, TextData, ApplicationName, LoginName
,CASE WHEN a.Success = 0 THEN 'No' ELSE 'Yes' END AS 'Was Successful'
FROM master.sys.fn_trace_gettable(
( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) + 'log.trc'
FROM sys.traces
WHERE is_default = 1
), DEFAULT) a
WHERE a.Success = 0
