Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

While I understand the purpose of applying permissions to roles and then adding a user to a role, I'm wondering if there is any value in doing this if it is known there is a one-to-one relationship between each role and each user.

ie. If you know there will never be more than one user in a role, does that make roles redundant and you're just better off applying permissions directly to the user.

share|improve this question

4 Answers 4

up vote 6 down vote accepted

What if that user changes (retirement, getting fired, etc.)? What if times change and you do have to give the same permissions to another user?

That's why the recommendation is:

Windows user -> Windows security group -> SQL Server Login -> Database User -> Database Role -> Permission.

share|improve this answer

If your user accounts are really role accounts (meaning they have generic names like "manager" or "updater" and aren't attached to real people), then you might be able to do this. However, in any organisation there will always be people moving around for one reason or another, either temporarily or permanently, and it will be much easier to manage the permissions using roles.

share|improve this answer

An advantage of using roles on SQL Server databases is, that they are preserved in the database. If you had to restore database backups on other server which lack some users, you'll have trouble with permissions. Roles can be assigned to existing user on the new server.

share|improve this answer
    
Technically, the users are still preserved within the database so you could script out the permissions and re-apply to the new users. But that's a tremendous amount of work when someone could simple just sp_addrolemember and be done with it. –  K. Brian Kelley May 14 '09 at 10:33

"Role" is there for convenience. Apply rules once, then add/remove users will become an easier task. The 1:1 Role-User scenario does not exist in the real world.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.