How SQL AD Group Security promotes Clean DBA Living.

We know that we can improve auditability and simply database security administration by doignt he following:

Eliminate SQL Server authentication in favor of integrated security; and

Create AD Groups, and set those groups up as logins and database users, with specific permissions granted and or denied as the business requires; and

Assigning AD users to those groups.

But why the extra level of AD Groups? Why not just add the AD users as database logins with specific permissions at the more granular user level?

We could certainly do that, but the administrative tasks that come from this approach grow geometrically.

AD Groups reduce SQL Login maintenance.

Sheer Overhead

Let’s assume that you have 100 AD users who can access 4 databases on 2 SQL servers each. That is the size of a very large workgroup or a small enterprise. Without a grouping approach maintained at the AD level, you have to create 200 logins, 400 database users, and assign roles or (worse) individual permissions to each of these users. That is a boat load of maintenance work.

On the other hand, grouping means that the user’s DB access is provisioned with his or her AD profile, and DBA has no involvement aside from a one-time provisioning of the access to the DBA group.

DBA resources are freed to work on more pressing issues.

Orphan Prevention

Assume for argument that a person is terminated and his or her AD account is deleted. That does nothing to delete the SQL Server logins or database users. You now have an orphaned login, and if you drop the login you have orphaned database users.

While it is true that you can write or download T-SQL scripts that can check AD for missing logins, identify orphans, and sweep the databases to drop users and then logins, this is maintenance work that requires attendance and usually automation in the form of SQL jobs that run regularly.

On the other hand, if the users are organized into AD Groups and one of the logins is deleted, there is no SQL Server maintenance at all.

Auditability Redux

Assume that Jane Doe is promoted from Payroll assistant to sales manager. Jane no longer needs access to the payroll tables, meaning that her roles must be individually maintained by DBA, separately and in addition to any maintenance of her profile in Active Directory. Worse, if the roles don’t cover everything and there are individual GRANT commands to tables and other entities that need to be revoked, Jane’s simple change results in significant script work for the DBA.

On the other hand, if you have an AD Group DOMAINPayrollWorker with all permissions set there, then the network admin who provisions Jane’s profile need only drop her from the group and her permissions change instantly.

That kind of control over access is the kind of stuff that gives auditors cause for celebration.

Conclusion

I’ve previously discussed the security benefits of eliminating SQL Server authentication. Combined with the approach of Active Directory group and role based authentication, the security benefit also comes with a savings in DBA time.