The 183^rd edition of T-SQL Tuesday is hosted by Steve Jones. He has picked everyone’s favorite (cough) topic: permissions, and how to manage them.

I am lucky. With my specialization in query performance tuning, I don’t often have to deal with permissions. (Well, apart from of course not getting the permissions I need to do my work effectively).

But I have been in contracts where I was the only employee able to spell SQL, and hence all other database tasks also fell in my lap. Including permissions.

And yes. I have been in projects where the idea was to investigate all current permissions, check which are and which are not needed, and then correct everything that was wrong.

We never got past stage 1. Even in a moderate sized company, with moderate sized database applications, getting a full overview of who has which permission was sheer hell.

Permission hell

Just getting an overview of permissions is HARD!

First, because the permission structure is complex. One might have select permission on a schema. But perhaps that permission has then been denied on individual tables in that schema, or on certain columns. And what if the user is also in a database role that grants additional privileges?

But let’s also not forget that identities are hard. What if you are in a windows group, and that windows group has been assigned permissions that don’t align with what has been assigned to your user? What if you use an application that uses an application user to log on?

The fact that it was virtually impossible to make an overview of actual permissions for everyone was due to the chaotic way permissions had been set up. Every feature that can be used, was used. The fact that we never got to the stage where we could restructure and make things easier … okay, less hard, to manage, was due to lack of guts to make decisions in the organization. Because we never were able to fully inventory all permissions, but also because no manager dared to make a decision that might backfire, the project simply fizzled.

However, I did know, very well, what structure I wanted to move to.

Permission heaven

Okay. I lied. There is no such thing as permission heaven. Permissions are complex and managing them will always suck. But we can at least try to move from the first circle of hell back to perhaps the fifth or sixth.

So, here is how I believe permissions in SQL Server “should” be set up.

You start by defining each job role. For every job role, define exactly what permissions are needed. This is all preparation work, you don’t have to open SSMS or ADS for this.

The next step is to create database roles and server roles for each job role that was identified in the organization. Then you assign the required permissions to each role. Once that is set up, the only thing left to do is to add user accounts to the database and server roles that match their job role or roles.

Removing permissions that are no longer needed is often overlooked. Generally, employees that have been through several positions in the company will have way too many permissions. Simply because it is too complex to identify which permissions are no longer needed. New permissions are added (usually after a ticket that someone can’t work). And then they linger forever.

When all permissions are given to roles only, and all users are added to roles, then it becomes a lot easier to manage permission from someone who switches roles. Add them to the role for their new job, and remove them from the role of their previous function. It is still a human process and it is still possible to overlook and forget it. But there is now far less excuse for doing so!

It is very important to do this with zero exceptions. A permission that applies literally to just a single person? Perhaps the CEO or the single DBA? Cool. Create a CEO role or a DBA role, assign the permission, and add the CEO or DBA to that role.

The next step

You can even take this one step further. And I recommend you do, even though this is outside of the database realm that is your official responsibility.

The Windows security officer should do the same. The terms are different, but the idea is the same. Create a Windows group for every job role in the company, and assign appropriate rights. Create users for individual employees, and add them to the groups that match their function. Don’t assign rights directly to logins.

So, if your Windows administration team has set up security like that, do you still need to use roles in your SQL Server databases? Strictly speaking, it is not needed. You can create logins for each Windows group, rather than for Windows users. And then assign rights to those Windows groups directly, without using database or server roles. But … this has its downsides!

The first is auditing. If your SQL Server logins is for the “Sales representatives” group, then every modification made by any sales rep is logged under that login. It is impossible to see whether it was made by Shankar or by Jane. So perhaps you still want people to have their individual logins on the server.

The second potential downside is the dependency you create. Suppose that you have an urgent ticket that needs to be handled yesterday, because an expensive consultant needs rights they don’t have now. Do you really want to be dependent on the windows security admin responding timely, when your neck is on the line for that consultant being unable to work?

Conclusion

Whether on windows or in SQL Server, permissions should always only be assigned to entities (server or database roles, or Windows groups) that correspond to the job roles in the company. The actual individual employees (server logins; database users; windows users) should be added to those roles and groups. You should never assign rights to any individual person.

Strictly keeping to those rules will not make security easy. But it will make it a lot less hard. And you might even be able to convince the organization to not just add more permissions as needed, but to also take them away from people who no longer need them. Because that is now as simple as removing the login or user from the role or group.

Thanks, Steve, for hosting this edition, and for forcing me to think about one of my least favorite topics!