SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Worst Practices - Assigning Users Rights

By Steve Jones,

Assigning Rights to Users:

This article continues the series on WP.

This is one of my pet peeves. One of the few things that I feel is just plain poor development and administration practice. And it is something that is incredibly easy to avoid, taking almost no more effort to avoid than it does to implement. And yet, it is one of the things that I constantly find implemented by virtually every network administrator, developer, and DBA that has not worked in a large-scale environment (and some that have).

Why not assign the rights to an individual?

I am constantly amazed that I have to explain this to people. I am even more amazed that when they hear my argument and agree with me, that they do not bother to implement it. I am also constantly amazed that anyone even bothers to allow the assignment of individual rights in their systems, especially vendors.

First of all, let's agree on one thing. In any company, or any system for that matter, the system will likely survive longer than any individual person. By nature, a company or a system has no real lifetime, often extended even beyond what anyone anticipated. A person, however, has a life that changes, has priorities that change, interests, emotions, etc. that all lead one to move on to a different position, different company, or even industry. If you think about all the systems on which you have worked. How many have you taken from scratch to retirement? I don't think I have ever seen both the initial development and the retirement of a system.

So if an individual will not survive the system, wouldn't it make sense to design the system so that the individuals are easily replaceable? This brings us to rule 1:

Rule 1: Design the system do that individuals are easily replaceable. Don't integrate the individual account into the security of the system.

I realize that it is easy to remove a user from a system. A single sp_droplogin will accomplish this task. However, it is rare that people drop out of a system. Usually they move on and are replaced. So now if you have assigned individual rights, you must grant all the same rights to the new person. I realize that SQL Server has a GUI for managing the system, but the rights assignment GUI is one of the worst I have ever seen. For a database with any large number of objects, this is a cumbersome and frustrating method of assigning rights.

Set aside the difficulty of using the GUI and assume everything is scripted. Suppose that you have a script to assign these rights. You merely place the new person's account in the script and run it and things are set, right? True, but suppose you create a new table, which usually means that you will also create some stored procedures or alter others. You now need to add the rights for these items to your script. Suppose you have just 20 users with 2 different sets of rights. Now you have at least two commands to run. This process is much more complicated than assigning the rights to a role. Something that can be much more easily maintained.

Rule 2: Assigning individual rights leads to an unnecessarily complex rights assignment/management process.

The last reason for not assigning individual rights is more of a corollary to the second reason. An unnecessarily complex process is hard on administrators. All of us are human, and all of us hate to do things that are difficult. While there are some people who will continue to follow a procedure and adhere to a routine no matter how dull or inane it seems, most of us will not. Most of us will take shortcuts, we will circumvent the process, or we will cease to maintain it.

Rule 3: An unnecessarily complex process will not be maintained.

So what's the best practice?

] While building SQL Server 7, the SQL team realized that the single group rule for users from prior versions was too inflexible and did not meet the needs of many systems. The security system was enhanced to more closely model the NT network model where a user can be the member of more than one group and their rights are the sum of the rights of all the roles of which they are a member. While redesigning this model, a number of common roles that are often used were included. For example, the datareader model automatically receives select rights for all tables in the database.

This model allows an administrator to develop roles that match the rights needs of various user groups or departments. If a user needs rights from more than one role, you assign them to multiple roles. This is the best practice for managing your security. Build a role for each type of access that you have. If new rights for a few or even a single user are needed, create a new role, assign the rights to the role and assign the role to the user.

The only catch with designing rights is that the DENY right overrides other rights. If a user is a member of Role A, which has been granted select rights to table A, and also a member of Role B which has select rights denied for Table A, they will not be allowed to select data from Table A.


Good security is essential for any database system. Assigning rights to individuals is a worst practice that can compromise security by implementing a difficult to maintain system. Avoid this worst practice by using roles to handle all of your security needs.

While I do not really see any reason to assign an individual rights, there may be cases where it is necessary. I would love to hear from any readers who have cases where they feel individual rights should be assigned. Please respond using the "Your Opinion" button below and I will be happy to debate the subject.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.

Steve Jones
┬ędkRanch.net October 2001
Return to Steve Jones Home


Total article views: 7225 | Views in the last 30 days: 0
Related Articles

Error creating a user and assigning rights.

Error creating a user and assigning rights.


Worst Practices - Assigning Users Rights

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...


Practicing Deployments

Amateurs practice until they can get something right. Professionals practice until they don't get th...


Best Practice for Database Owner?

Is sa the best login to assign for production databases?


Worst Practices - Blank Passwords

Not even worth talking about, right? Probably, but they still exist. Read about this worst practice ...