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
Best Answer
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:
Sysadmin, dbcreator and db_owners role members automatically get privilege for using SHOWPLAN. Details here.
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.Here is my 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) .