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.
- MyDomain\Dev
- MyDomain\SQLAdmins
- MyDomain\Reporting
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.
Additionally, you have built a good rapport with other IT members by collaborating with them to improve the work environment!
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
- Review this link for examples from the Microsoft docs about this topic:
- Review your environment and talk to your System or Cloud Administrators about how they can help make managing SQL Server better and easier for everyone.
- Check out my services page for other ways I can help.
- If you would like help with anything in this post, or with something else related to SQL Server, reach out to me on Twitter or Linkedin and I’ll be glad to offer assistance.