I'm using SQL Server 2008 R2.

Is there an option in SSMS 2016 to toggle SHOWPLAN permissions for logins (I know this is issued at the database level)?

Is there a query to see if a user, other than myself, has SHOWPLAN permissions?

I know of fn_my_permissions, but am looking for something similar that can be ran against another user account. For security reasons I am unable to use EXECUTE AS

  • what you mean by toggle SHOWPLAN permission for logins? – SqlWorldWide May 1 '17 at 17:19
  • In SSMS Object Explorer > Instance > Security > Logins > A login's properties >: There are server role toggles, User Mapping for DB role toggles, and Securables for other types of permission toggles, e.g. Alter Trace. I'm wondering if there is an option anywhere in SSMS to toggle SHOWPLAN permissions on. – Dave Sims May 2 '17 at 15:17
  • Not directly unless you make them a member of groups that I explained in my answer. – SqlWorldWide May 2 '17 at 15:20
  • Where is the option? – Dave Sims May 2 '17 at 15:25
  • Under server roles but you will be giving way more privilege than just showplan. Why cant you run tsql GRANT SHOWPLAN TO <database_principal> [ , ...n ] – SqlWorldWide May 2 '17 at 15:27

Is there a query to see if a user, other than myself, has SHOWPLAN permissions?

Before answering this question lets first see what privileges you need for using SHOWPLAN.

You can grant it by using:

TO <database_principal> [ , ...n ]

Sysadmin, dbcreator and db_owners role members automatically get privilege for using SHOWPLAN. Details here.

For SELECT, INSERT, UPDATE, DELETE, EXEC stored_prodedure, and EXEC user_defined_function statements, the following permissions are required to produce a Showplan: Appropriate permissions to execute the Transact-SQL statements. SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements, such as tables, views, and so on.

Now to answer your question about "a query to see if a user, other than myself, has SHOWPLAN permissions?"

You will need to find if the user is a member of any of these 3 roles or SHOWPLAN privilege is granted explicitly.

exec sp_helpuser 'test' will give the list of roles a user is a member of. For explicit privilege you can use this and check if SHOWPLAN privilege was grated explicitly. This script is modified from here.

USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE USER_NAME(dppriper.grantee_principal_id) ='TEST'

Here is my test:

USE [master] GO CREATE LOGIN test WITH PASSWORD  ='tes545###45454545454t'  
USE [WideWorldImporters] 


Now run the query above and you will see the explicit SHOWPLAN permission for user test.

Once you run above tsql you will be able to see the permission for the securable (database name is this case) in the bottom window 'permission for .....' (you have to add the database as a securable first) .enter image description here

  • The query you provided seems to work. Thank you. I'm still curious if there is a toggle in SSMS anywhere, but that probably should have been a second question, although it was my title. Thank you. I will wait a few days before choosing the answer. – Dave Sims May 2 '17 at 15:31
  • The toggle is there for users rather than logins if you examine Permissions tab under database properties, lot of clicking about on each user for each database though. – Gareth Lyons May 2 '17 at 17:29
  • Yes I agree, but that is the best I found if you are depending on the GUI. – SqlWorldWide May 2 '17 at 17:51

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.