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

Add them to ALL the roles!

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.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: microsoft sql server, role, security


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...