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

The Proliferation of Roles

By Steve Jones,

The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I've always followed this guidance in my career. I've learned that if one person needs access, sooner or later someone else will. Even if it's an automated process, I'll use a role so that I can build a test login to ensure I've configured things correctly.

In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

However I also have had people complain that if each new required position or process needs their own role, sooner or later we'll have this proliferation of dozens of roles.


I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn't bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

However, I know they can be complex, so I'm wondering if you have any tips, tricks, hints, or even gotchas for using roles. I'd also be curious if you think there are cases where roles don't make sense.

Total article views: 62 | Views in the last 30 days: 1
Related Articles

How to prevent our database to access any person

How to prevent our database to access any person while other person is administrator


4 Things I Wish I Knew Sooner

I was recently called out by Tim Costello on a blog series started by Mike Walsh.  http://www.interw...


How To Lock in Sql Server 2005 The record When It access by Other Person?

How To Lock in Sql Server 2005 The record When It access by Other Person?


Monitor Query Performance

Blog post #7 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Sooner o...


Four Years Later

Comments posted to this topic are about the item [B]Four Years Later[/B] I live and work in Africa a...