Three Reasons to Use Windows Groups to Manage SQL Server

Ever come across a SQL Server that had 5, 10, or more individual logins for users in it? I have a few times and it made me wonder if whoever had done such a thing realized how much harder they were making the administration of the server by doing this. I suppose some folks start out with one or two people that they give special permissions or access directly to the SQL instance and underlying databases. Their logic probably goes something like, “It’s just Tommy and Suzie. Those are the only people who will need access for a long time.”

Initially I have to agree with this thought. When you only have a couple of people, it does seem simpler to create a login and corresponding user for those people. However, SQL Server access and permissions rarely stay that simple.

Hopefully you are in a growing company. A growing company will eventually employ additional people who need access to the database. If you are already in the habit of granting direct access using individual logins and database users, it will be tempting to continue this pattern. Before you know it, one day you connect to SQL Server and there is an ever increasing list of individual logins that suddenly catches your attention. Most likely there are a variety of permissions applied to those logins and users. This becomes a management nightmare.This is where Windows groups comes to your rescue.

Use Windows GroupsTo Make SQL Server Management Easier

Windows groups make managing your SQL Server easier in several ways. We’re going to look at three specific ways Windows groups help.

1. Shared responsibility for SQL Server security

In a typical environment there will be Developers, one or more DBA’s and perhaps folks who write SSRS reports, use PowerBI, or who do ad hoc type reporting. These groups of people most likely all need different permission sets. Have your System Admininstrator create Windows Active Directory groups.  Ask the SysAdmin to add the developers to a MyDomain\Dev group. Ask her for MyDomain\SQLAdmins and perhaps MyDomain\Reporting.

Now let’s suppose that the company has a new project and three Developers are being hired. In the past, the DBA might make three separate logins and map those logins to database users. Instead, now that MyDomain\Dev exists, add the group to the Dev SQL Server with an appropriate role, like SysAdmin server role. Now, just ask your System Administrator to add those new developers to that group while they are in Active Directory creating Windows accounts for the new employees. Just that easily, permissions are set before the new employees show up on the first day. Prod permissions are set in the same way when you add that same group to prod and apply an appropriate database fixed role like db_datareader.

Now that you’ve gone down this path, the larger IT team is engaged when thinking about SQL Server security, and it will make your life easier later.

2. Centralized permissions

Using AD groups centralizes the management of permissions. In SQL Server, add these logins and map them to appropriate database users. For the Dev group you gave them only db_datareader to various databases. If that ever changes, you simply apply the changes to one place, the AD group in SQL Server, and not to all of the developers individually. You can do the same for MyDomain\Reporting. Why have two groups with the same permissions you ask? Well, it makes permissions management easier. If you throw them all into the same group, then when the Developers need slightly different permissions for something, now that permission change would be applied to your reporting people too. You may not want that. You also only touched SQL Server once to add the group and the permissions for that group.  You’ve just saved yourself some time so you can go do something else.

3. Separation of duties

Back in SQL Server 2008 the BUILTIN Administrators group was removed from SQL Server. This means that local administrators are no longer granted automatic access to the SQL Server instance. This was done to enhance security. Given this change, you might want to create a group specifically for your DBA’s. Then grant the group the sysadmin server role inside SQL Server. This is where MyDomain\SQLAdmins comes in. You can grant them the needed access to manage the SQL instance without having to make those folks admins on the machine. The Windows SysAdmins can retain control of the machine without automatically being granted control in the SQL instance. Everyone is happy, right?

 

Next Steps to Take

  1. Review this link for examples from the Microsoft docs about this topic:
  2. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me on Twitter, and I’ll be glad to offer assistance.

Leave a Reply

Your email address will not be published. Required fields are marked *