John, lots of good stuff, but maybe a bit of a mixed message too based on the title. I'm all for removing sql authentication, but in practice it has rarely been a realistic option. It's not on because I'm lazy (or the DBA before me), but because something is using a sql login and I can't get the business (or the 3rd party vendor) to invest the time to change it. Is it a lot of time? It shouldn't be, but it its more than just changing the connection string. It requires testing, may impact reports and packages, etc, etc. It's a tough sell to fix something that is working vs working on something that is perceived as adding value.
I like standardizing groups and delegating group membership. I've commonly used patterns for "-r, -rw, and -dbo'. Getting the right pattern takes some understanding of the environment. For example, I have an instance with several hundred db's that are the same schema and all used by one app, we use the same group in each - its a db level group, sorta! In other places it would be a different group per db to really partition access. Having a script to generate whatever pattern of names is smart, good stuff!
Adding to that, I typically request that only app/report servers and jump boxes have direct access to SQL (firewall rules). That way, AD credentials or not, it limits the ability of users to run queries from hell. In practice some users will get that access, even on production servers, but it doesn't have to be open for everyone to try.
From a compliance perspective removing (or even reducing) sql authentication is a win, as is having AD groups that clearly indicate the business use and access, the latter being important when it's time to recertify everyones access (quarterly or yearly).
I saw an earlier post about the validity of AD groups being "more secure". It's a fair question. I think AD is more secure when managed service accounts are used - no one has the password. After that, from an administrative perspective I see AD as being better and I think that is the selling point to use, though to be fair everyone auditor has been trained to think "sql logins" bad and so removing them does make life easier.
Thanks for the article.