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?


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.

  • Yes I saw that. I'm just confused as to what queries exactly would be returning plain text passwords. How to mitigate this. How a 'query trace' or recording queries could be prevented (is that really necessary to tune up queries via the showplan? Unfettered recording to all activity on the database?) Like most technical stuff, devil's in the details.
    – user45867
    Feb 8 '18 at 18:40
  • It wouldn't be so much that they return plain text passwords, but you might be doing a SELECT or a UPDATE on the field referencing a plain text stored password in the WHERE clause Jul 11 '18 at 22:09
  • 1
    The right answer is to store salted hashes for user databases, and not to use a SQL Server instance as a credential store for other systems.
    – Emyr
    Jan 17 '20 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.