4

I have a group of 5 developers that need to be able to view the plans of queries they have written for a database, and that database only. I refuse to make them a member of sysadmin. I do not want them to be able to run a trace, ever, on this server, nor do I want them to be able to run dmv's.

These 5 developers are members of a windows group. I have made them a member of db_owner.

This is what I have found:

  • A server principal with DENY for server permission ALTER TRACE will encounter Error 262 when attempting to view SHOWPLAN output
  • This happens even if the database principal has a GRANT for SHOWPLAN database permission
  • This happens because SHOWPLAN permission is implied by ALTER TRACE permission
  • This does not happen if the database principal is the database owner
  • A server principal with GRANT for server permission ALTER TRACE will be able to get SHOWPLAN output without needing to explicitly granting SHOWPLAN for individual databases

Only sysadmin and if you are mapped to the dbo user name within the db, does it not perform the check.

Is there a way to give these developers the ability to view plans without the role of sysadmin or granting alter trace or granting view server state?

  • Why do you need to explicitly deny alter trace? – Aaron Bertrand Oct 2 '14 at 3:37
  • Because I don't want them to be able to run a trace and see stuff going on in the other databases. This is a hospital. – stacylaray Oct 2 '14 at 3:39
  • 1
    I still don't understand why you need to explicitly deny alter trace. Just don't grant it. Then there will be no conflicts with the database-level SHOWPLAN permission (and the users still won't be able to call things like sp_trace_getdata). – Aaron Bertrand Oct 2 '14 at 3:42
8

DENY trumps GRANT, so yeah, you can’t let people view plans if you’ve explicitly denied them the ability to alter trace. But just because you haven’t denied a right doesn’t mean it has been granted.

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE.

It also shows that, unless you explicitly grant any trace-related permissions, they won't be able to read trace data, or even be able to tell there are traces running at all, unless they have inherited those rights in some other way (Windows group, server role, etc). So you don't need to explicitly deny ALTER TRACE in order to protect trace data from these users - just be aware that it is possible for them to can inherit it without an explicit grant.

USE master;
GO
CREATE LOGIN blob WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blob FROM LOGIN blob;
GO
GRANT SHOWPLAN TO blob;
GO
CREATE TABLE dbo.splunge(plonk INT);
GO
GRANT SELECT ON dbo.splunge TO blob;
GO
EXECUTE AS USER = 'blob';
GO
SET SHOWPLAN_XML ON;
GO
-- plan is shown:
SELECT plonk FROM dbo.splunge;
GO
SET SHOWPLAN_XML OFF;
GO
-- error:
EXEC sp_trace_getdata @traceid = 1;
GO
-- empty result set:
SELECT * FROM sys.traces;
GO
REVERT;
GO
USE master;
GO 
DROP DATABASE floob;
DROP LOGIN blob;

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.