I've been struggling to find best practices information or any advice regarding Active Directory Groups that provide group members with the SysAdmin server role. I'm at a new job where I'm the only SQL Server DBA for 50 – 100 instances of SQL. There is one other (network) system administrator that will have the SysAdmin server role. There may be others in the future as well (may or may not be DBA's).
I'm picturing two options:
Option 1: create a group in Active Directory, add myself and any others to the group as needed, create SQL Server logins on each SQL Server instance for the AD group, and give the logins the SysAdmin server role.
This is convenient, and allows some flexibility. But it seems like a huge security risk. As a DBA, I don't have full control over who is in (or out) of the AD group. Currently, each SQL Server instance has a handful of logins with the SysAdmin role. I want to take SysAdmin away from all of them except for me, the network system admin, and sa. If I accomplish this task and implement Option 1, I end up no better than where I started from.
Option 2: use individual Windows Authentication (AD account) logins on each SQL Server and give each the SysAdmin server role.
This is not so convenient, but only two people/AD logins have SysAdmin privileges. As long as neither of us grant SysAdmin role membership to others, there should be a certain level of order.
Which option is preferred?
Are there other options I've overlooked?
Best Answer
Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.
By abstracting your access to AD groups, you gain two benefits: