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