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) .
GRANT SHOWPLAN TO <database_principal> [ , ...n ]
– SqlWorldWide May 2 '17 at 15:27