Sql-server – Granting permission to SQL database

Take a look at Showplan Security in Books Online, which says:

Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information.

For example, consider the following query:

SELECT COUNT(*)  FROM table_1  WHERE column_1 < 10 

If a malicious user produces Showplan output for a set of queries like this example, and replaces the value "10" in the predicate with different constants each time, the user could infer an approximate data distribution of the column values for column_1 in table_1 by reading the estimated row counts.

This risk does seem negligible, especially given this is a development server, where all queries would be from devs and IT people, not users.

Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx

The chart shows that db_datareader role is identical to GRANT SELECT ON [database]. So it is still fine to use, but the recommendation is to move away from those roles to the more granular commands. Some of the other fixed database roles are less clearly defined for most people. Using the explicit commands results in greater clarity when reporting rights.

Obviously you know how to grant finer grained permissions. I am trying to break loose from the old roles whenever possible, but db_owner (for example) is a hard habit to break.