SQLServerCentral Article

Worst Practices - Assigning Users Rights



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


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


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


Rule 3: An unnecessarily complex process will not be


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


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



5 (1)




5 (1)