Steve Jones - SSC Editor (7/31/2014)
Eric M Russell (7/31/2014)
If the windows group like 'MyCorp\ProductionDBA' or 'Builtin\Administrators' are members of SYSADMIN, and user account 'MyCorp\JohnSmith' is added to one of these groups, then he has sysadmin privillage. There is no 'CREATE LOGIN..' or 'GRANT..' operation, and as far as I know, there is no profiler event, extended events, trigger, or meta-data change within SQL Server that could be leveraged to alert this at the time the domain group membership is added.However, one thing that could be done is to create a LOGIN trigger that checks the sysadmin privillage of an account at time of login and then compares user's account name to a table containing list of known admins.
No WMI-type event? Something like this?
http://msdn.microsoft.com/en-us/library/aa772153(v=vs.85).aspx
That looks promising for monitoring changes to a specific domain group that we know has membership in mssql sysadmin role. However, I'm not sure it would cover local groups like 'Builtin\Administrators'. That's a strong and compelling reason to not add local windows groups or accounts to a privillaged SQL Server role, and why they are not longer added by default in recent editions of mssql installation process.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho