Security in SQL Server, at its core, is a pretty simple topic (not in actually configuring and getting it right, but the mechanics of how security works.) There are server and database principals, and you can grant, take away, and deny rights to these principals to do pretty much anything at the server and database level. In this blog, I will take it as expected that you have a solid understanding of such things.

Where things get messy is that you need to use different security principals (usually Windows logins for SQL Server, but that is certainly no requirement) for your different environments. Coders need to be able to change objects, and then test access via a different user. Testing should mimic production, but the same principals shouldn’t be in TEST and PROD, lest you mess up and use the wrong one. To make matters worse, your typical login is probably a user in applications that you can also make changes to in a DEV environment. Yeah, things get messy, no matter what model you choose for securing your PROD data:

1. Create one user and give it all rights to the database
2. Create specific users and give them the least amount of rights to do what is must, and no more
3. Somewhere in between the previous 2

Truly, #3 is generally the answer. Let’s say that you give the application all the rights that any user of the system can have, and let the application dole out the rights to individuals. This is not a terrible plan, but I dare say that many databases contain data, or utilities that it is not desirable to give to the users. (My utility schema generally has tools to maintain and release code, something that you don’t want general users to have access to. And lest you have a developer working “with” you like I once did, you don’t want the application to have access to the tools to disable all of the constraints in the database, even if you have ETL uses for that code.)

Generally, this means we have multiple logins (Windows, and sometimes SQL Authentication) that have different levels of access in different environments to code, test, and actually use the applications to get business done. Each environment needs to have different users able to access the system in different ways. For example, say you are building the company X application. In DEV you might be a developer, and in PROD, I may either be a business user, or even disallowed access altogether. In QA, I may log in as a test user who has access like a business user. If these all have the same access, one whoops and you may have just lost a lot of data (and maybe a job).

To effectively test the security and make sure that DEV, QA, and PROD will work as designed, the design goal is to make sure that security it matches in each environment as much as possible, without using the same security principals. So let’s say we have Database1, which has 2 tables in it: Demo1.Table1, Demo2.Table2.

Then we want to allow UserProd1 to access Table1, and UserProd2 to access Table2. For sake of simplicity, we will ignore QA for the scenario.

This seems easy enough, we just do the following (after creating the two users):

Now we need to test this, so we create users to mirror UserProd1 and 2, call them UserDev1 and 2, then give them access in Dev:

Simple enough, if you just have two users and two tables, sure. But you cannot check in this security in with your schema, so you are leaving it to scripts to be tested. And if you have 100 tables, and 2000 users, we are getting to nightmare country. Instead, what we do is create database roles for the groupings of security that we wish users to have. So we might create:

This role is completely devoid of environmental information, and will be 100% the same in DEV, PROD, and any other environment you use to test your security. Hence, it can be checked in as part of the database, and unit tests can be employed to make sure it works. For example, the following script tests that the security will work for the two tables we created:

Which returns:

(0 rows affected)

Msg 229, Level 14, State 5, Line 22

The SELECT permission was denied on the object 'Table2', database 'TestSecurity', schema ‘Demo2'.

As desired. After you have tested your security, you can check in the roles into source control, and the only things you will need to vary by environment (and hence, keep a script around for,) is granting users access to a role.

Note that I put the two tables in different schemas. You can simplify your security if you have used schema to just grant rights at the schema level, as in:

Now any objects added to the respective schemas are automatically accessible by role members.

One thing you will also likely have is one or more roles in dev that don’t show up in production, which you will need to work with your source control tool/process to ignore. For example, you may have a role named Developer in DEV that has lots of power in DEV, but ideally doesn’t exist in your PROD databases (you could release it to PROD and have a policy that keeps it empty, or a job to clean up access after temporarily granting powers for a release). Using a tool like SQL Compare for releases, you can program your template to ignore an object by a given name.

So for DEV you may have a file like:

And for PROD:

Since you could test that the roles work as desired, you know that whatever user us placed in the role will have the desired rights.

Tip: The users in the database can be mapped to a Windows Login, or a Windows Group. Using a Windows Group will allow you to remove most of the administration of rights in your SQL Server. So if your dev file was changed to:

Where the [Domain\DevUsersN] was a Windows Role, the Windows logins that were placed in the role would get the access that you configured to your role. And now managing your SQL Server security is simply a domain administration task. You have tested the roles do as desired, and security is pretty much a straightforward task (though it is not going to be easy to set this stuff up the first time!)