Sql-server – What are SHOWPLAN permission security risks? (MS SQL Server)

permissionsSecurityshowplansql serversql server 2014

I'm having some friction when it comes to showplan permission security.

Say there are 5 databases on a server. And each one has a test/ production split (so 10 total).

There is one sysadmin.

There is another person (non-sysadmin) who is responsible for the data, queries, tables, views, design, structure, indices, etc on DB Number #1. And that alone.

Do you grant SHOWPLAN/ View Server State permissions on DB Number #1 to the person responsible for its design, query optimization, etc? Or does that present an unacceptable security risks to a non sys-admin?

Perhaps it's a bit subjective or a gray area, but I wasn't sure. Are there specific patches/ procedures that can be put in place to mitigate these risks?

Are there any alternatives to SHOWPLAN when optimizing queries and query run time? What about sys.sql_expression_dependencies, also a related View Server State permission, correct?

Some may argue that you have a a DB_Development, DB_Test, and DB_Production split. Perhaps ideally, but when you only have the resources to implement a Test/ Production split, maybe access on Test alone is sufficient risk mitigation?

Best Answer

You can read more on the security risk in: SHOWPLAN Permission and Transact-SQL Batches:

Security Note
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.

Related Question