Three Reasons to Use a Custom Database Role

Ever had a situation where a small in-house application written for a small group of employees needed some permissions in the database? How do you handle that? I want to walk through the scenario and provide three reasons that using a Database Role might be the answer you are looking for.

Let’s first cover, at a high level, the different levels of permissions in SQL Server and the associated commands. In SQL Server there are Server level permissions for the SQL instance and there are database level permissions. Within the database level there are also specific permissions for objects like tables, views and store procedures. There are also database permissions that can be grouped together in a database role. In fact, there are some built-in database roles for SQL Server as shown below. If you aren’t familiar with these, you can find out about them here.

Database Roles

 

As an example, suppose there is an application being written for a small department in your company. The application needs access to your company database, but not access to everything in the database. Perhaps the app only touches a few tables. Someone could grant the Server level permission of SysAdmin to a Windows account or Windows group that contains the necessary people added to it in Active Directory. That would be a “sledge hammer” approach to providing permissions and break the principle of least privilege. In this scenario, a database role could be used to accomplish the task at hand.

On your domain you or the System Administrator can create a group called MyDomain\DepartmentName. With the pseudo-code below as a model, you can create the Login and User.

USE master;
GO
CREATE LOGIN [MyDomain\DepartmentName] FOR WINDOWS
USE MyDatabase;
GO
CREATE USER [MyDomain\ DepartmentName] FOR LOGIN [MyDomain\ DepartmentName]

In this new AD group, you place the members of the department. This allows the department members the ability to use their already familiar Windows passwords, keeping it easier for them. Using Windows groups also makes it easier for you. If you aren’t convinced of that read this post.

At this point is when some people might still take the approach of giving big permissions by mapping the created database user to the db_owner role in the database and be done with the process of permissions. However, do you really want a group of people in a department to have complete control over the entire database. Somehow I doubt it, and I doubt your auditors want to see that either.

Permissions could be granted to the user account itself inside SQL Server, but what if other people who are not yet in the appropriate AD group want access? Now you either have to add them to that AD group, which might now make sense from an organizational perspective, or you have to start this process all over again with a new login and user. This is where Database Roles come in. Below is example code on how you would do that based on the process we already have in place.

USE MyDB;
GO
CREATE ROLE MyNewDBRole;
GO
ALTER ROLE ADD MEMBER [MyDomain\ DepartmentName];
GO
GRANT EXECUTE ON [dbo].[GetEmployees] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].[InsertEmployee] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].[DeleteEmployee] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].UpdateEmployee] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].[GetSales] TO MyNewDBRole;

 

So what are some reasons to use a process like this? First, the application will follow the principle of least privileges. This means that the application and the users of the application will only have the permissions required for the functionality of the application. This keeps your SQL Server more secure, which makes auditors happy.

Second, this type of process eases administration. When the application is expanded and more people begin to use it and for different purposes, just create a group like the first one demonstrated and add the group to the role. Grant any additional permissions needed to the role. No adding individual logins to the SQL Server and no applying permissions to many individual logins, which would be a pain. Now you have a single point of permissions administration within SQL Server – the Database Role. Additionally, if there are new employees in the department who need access to the application, simply add those people to the AD group and you’re done!

Third, you and the System Administrator get some face time!! Who doesn’t like a little collaboration?

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. It does seem simpler when you only have a couple of people to simply 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 either truly need or believe that they 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, and most likely there are a variety of permissions applied to those logins and users. This becomes a management nightmare.

In steps Windows groups. In a typical environment there will be Developers, one or more DBA’s and perhaps folks who write SSRS reports or who do ad hoc type reporting. These groups most likely all need different permission sets. Create a Windows group in AD, or have your System Admin do it. (You are on good terms with her, right?) Ask the SysAdmin to add the developers to a MyDomain\Dev group. Ask her for MyDomain\SQLAdmins and perhaps MyDomain\Reporting.

Now in SQL Server add these Logins and map them to appropriate database Users. For the Dev group maybe you give them only Public permissions in production at the SQL instance and db_datareader to various databases. Now, do the same for MyDomain\Reporting. Why have two groups with the same permissions you ask? Well, it’s a management thing. See if you throw them all into the same group when people on the Dev team need slightly different permissions for something now that permission change would be applied to your reporting people too, and you may not want that.

Back in SQL Server 2008 the BUILTIN Administrators group was removed from SQL Server as a group that was 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 and grant them the sysadmin server role. This is where MyDomain\SQLAdmins come in. You can grant them the needed access to manage the SQL instance without making those folks admins on the machine. The Windows SysAdmins can retain control of the machine without automatically being granted control in the SQL instance to do things that the DBA’s would frown upon. Everyone is happy, right?

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. Now that MyDomain\Dev exists 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 and presto, permissions are set before the new employees show up on the first day. Prod permissions are set and you have of course already added the MyDomain\Dev group to the development SQL instance in the sysadmin server role so permissions are set there as well. All of that without ever touching SQL Server to grant individual permissions to people. You’ve just saved yourself some time so you can go do something else.

I hope this has been insightful and encourages you to start thinking ahead now, even while your SQL Server environment is still relatively small. I think starting with Windows groups from the beginning will save you time and headache later.