I have a group of 5 developers that need to be able to view the plans of queries they have written for a database, and that database only. I refuse to make them a member of sysadmin. I do not want them to be able to run a trace, ever, on this server, nor do I want them to be able to run dmv's.
These 5 developers are members of a windows group. I have made them a member of db_owner.
This is what I have found:
- A server principal with DENY for server permission ALTER TRACE will encounter Error 262 when attempting to view SHOWPLAN output
- This happens even if the database principal has a GRANT for SHOWPLAN database permission
- This happens because SHOWPLAN permission is implied by ALTER TRACE permission
- This does not happen if the database principal is the database owner
- A server principal with GRANT for server permission ALTER TRACE will be able to get SHOWPLAN output without needing to explicitly granting SHOWPLAN for individual databases
Only sysadmin and if you are mapped to the dbo user name within the db, does it not perform the check.
Is there a way to give these developers the ability to view plans without the role of sysadmin or granting alter trace or granting view server state?