This doc explains the process and features of the Admin Role Login Alert. This process has changed from previous versions and works quite a bit differently. The changes made are to increase speed of queries for the user. The crux of the changes revolve around prefetching the alert data so that it's more readily available. We've seen where these queries can take several minutes to run and we want it to be more immediate. Therefore, we're prefetching this data once a day. How and where the data gets prefetched is detailed later.
Here's the order in which the SPs are called for this alert.
Why is this alert so important?
This alert closes a huge gap in SQL security auditing. Let's take a look at the recommendations by Microsoft for managing DB security. Users go into groups -> groups become logins -> logins go into DBs.
It'll be easier if we diagram it out.
Above it's easy to see what we're talking about. And it really does follow an excellent practice for managing security. But that diagram doesn't really outline the problem. Here's a simpler version that does.
You may recognize the above as more of a classic-style network diagram. The reason this outlines the problem has to do with the use of the cloud. In network diagram terms, a cloud is used to symbolize an area where you either don't know or don't care about the specifics of what goes on inside. Or maybe it would just complicate the diagram. So in this case, we're using the cloud to show that as DBAs we don't know what goes on with the AD side of things. This diagram asks the very important question: How many people are in those AD Groups? Because SQL Server doesn't tell us when someone new is added to those groups; nor does it tell us when people are deleted from them. We add the AD group to SQL and then the AD team handles the rest.
But what if someone accidentally adds a user to the wrong group and now they have sysadmin on 30 of our SQL Servers? Or what if someone needs sysadmin but got deleted from a group by accident?
This is a huge hole in the current model because someone could have sysadmin on countless servers for months and as DBAs we'd never know about it. I think you can see how dangerous that would be for the security of the entire network.
That's why this alert is so important. This alert works in conjunction with other Minion features to grab all the AD Groups and all the users in those groups to give you a flat list of users that have access to built-in admin roles in SQL Server. It even unwinds all the sub-groups. So even if someone is buried 20 levels deep inside an AD group, if that group membership gives them admin rights on any of the SQL Servers, you'll get this alert. And the same goes for users who get deleted from those groups and lose their admin rights.
This approach increases your security exponentially because now you've got full view into your AD groups and there won't be a big security mistake made that goes unnoticed for months, or until the auditors come, or worse yet - when a huge breach happens.
Here's a sample alert:
You can see in the alert above that the alert doesn't just show you AD groups, but also SQL logins and Windows users that have been given direct access.
Details of each SP are detailed here: