Does SHOWPLAN permission toggle exist in SSMS

Question

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

Answer 1

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:

GRANT SHOWPLAN
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.

SELECT
USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
dppriper.class_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.permission_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'  
GO
USE [WideWorldImporters] 
GO

CREATE USER test FOR LOGIN test  
GO 
GRANT SHOWPLAN TO test

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) .