The goal is to audit sysadmin logons as efficiently and safely as possible without auditing logons of other users, so as not to be logging excessive data.
I have found the information on auditing sysadmin logons by using a logon trigger, and in general that would accommodate our needs. However it does have the drawback of being synchronous, which means that users can't log on if the trigger fails for some reason, and it will slow down their logon a tiny bit.
With the logon trigger, I can use IF IS_SRVROLEMEMBER ('sysadmin') = 1
to dynamically determine if the user is a sysadmin, but something like this does not appear to be possible with audit objects. It appears that audit filters have to be a simple server_principal_name = 'domain\username'
(i.e., you can't use any SELECTs or functions to determine if the user is a sysadmin).
So the only thing left that might even be possible, as far as I've found, is using Extended Events, so I wanted to ask the community if this is something I should bother looking into. The event would probably have to capture all logons (again, as the filters can't run code), and then we'd have to have something that consumes the event to filter and log the relevant activity.
sysadmin
role should give you the same result. Am I correct? If yes, you can filter for thoseserver_principal_name
.WHERE ([server_principal_name] = 'CORP\ADMIN_1' OR [server_principal_name] = 'CORP\ADMIN_2')
is about as complex as you can get. You would have to specify all of the sysadmins by name, which is not impossible, but will require maintenance when staff changes are made.