Add them to ALL the roles!


July 21, 2014 by Kenneth Fisher

I seem to get a lot of permissions questions these days and one of the more frequent ones goes along these lines “I still don’t have the right permissions on database xyq.” So of course the first thing I do is use my handy dandy sp_dbpermissions stored procedure to check out all of their current permissions. Every now and again I’ll see a specific patern of permissions that always leaves me stunned. All I can assume is that a user requested “Add me to all of the roles” and a DBA not paying enough attention got click happy and did just that.

The list of standard database roles looks like this:

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin
  • public

So does anyone see a problem with adding a user to all of these roles at once? I mean other than the fact that if someone is a member of db_owner they really don’t need to a member of any other role. Did you figure it out? Remember that DENY overrides GRANT. A member of db_denydatareader and db_denydatawriter is not going to be able to read or write from the database even if they are a member of of the db_owner role. Now this is not the case if you are the actual database owner (or sysadmin) but those are exceptions to the rule. So the moral of the story is to only add users to the roles they actually need. Not just blindly add them to ALL the roles.

3 thoughts on “Add them to ALL the roles!

  1. […] Pack 1 Cumulative Update #11 is available! SQL Server 2012 with SP2 Slipstream ISO images fixed Add then to all the ROLES RestoreAutomation #Powershell Module Its mandatory Jim but not as we know it Detect and […]

  2. Wise Ole Man says:

    I’m not sure if it’s funny or it’s sad that a DBA would get click happy with permissions. I would certainly hope that DBAs would have higher regards for database security. Of course, having worked with many organizations over the years and many incompetent, unyielding, executives who are just itching to flaunt their power, I’d probably give them all the roles too — knowing fully well that “I’ve given them just what they asked for” while keeping my data secure from them.

    • I agree completely. I generally see this happening when someone is in a hurry and not paying attention, or really junior. It’s certainly annoying when it does though.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: