Is it possible to grant rights to DBCC TraceOn to a user

GokhanVarol 2013-02-28 04:50:14

I tried using a signed procedure around DBCC TRACEON command, tried execute as owner (sysadmin) that did not work then I removed the owner and signed it but that did not seem to work since DBCC TRACEON seems to require sysadmin rights.

Is there any way around this? I need to disable page pre-fetching scans for a specific workload (a procedure) and the user needs to be able to test the code.

CREATE PROCEDURE Common.DBCCTrace @Trace INT, @On BIT
WITH EXECUTE AS owner
AS 
SET NOCOUNT ON
IF @Trace = 652 
    BEGIN
        IF @On = 1 
            BEGIN
                DBCC TRACEON(652 /*652: Disable page pre-fetching scans*/)
                RETURN
            END
        ELSE 
            BEGIN
                DBCC TRACEOFF(652 /*652: Disable page pre-fetching scans*/)
                RETURN
            END
    END
IF @Trace = 8744 
    BEGIN
        IF @On = 1 
            BEGIN
                DBCC TRACEON(8744 /*8744: Disable pre-fetching for ranges*/)
                RETURN
            END
        ELSE 
            BEGIN
                DBCC TRACEOFF(8744 /*8744: Disable pre-fetching for ranges*/)
                RETURN
            END
    END
RAISERROR('Invalid Trace %d !', 16,1,@Trace)
RETURN 30
GO
SQLkiwi 2013-02-28 12:06:34
Yes signing works in this scenario, but you cannot test that it works by impersonating a user using EXECUTE AS.

You have to actually log in to SQL Server using a login that does not have sysadmin or CONTROL SERVER permission to test signing (in this particular case). Complete demo script below:

-- Create test database
USE master;
CREATE DATABASE Banana;
ALTER DATABASE Banana SET TRUSTWORTHY OFF;
GO
USE Banana;
-- Create the procedure
GO
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT IsSysAdmin = IS_SRVROLEMEMBER('sysadmin');
 
    DBCC TRACEON(652) WITH NO_INFOMSGS;
    DBCC TRACESTATUS;
    DBCC TRACEOFF(652) WITH NO_INFOMSGS;
END;
GO
-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
-- Certificate used for signing the procedure
CREATE CERTIFICATE cert_Test
WITH SUBJECT = 'Test certificate';
GO
-- Sign the procedure
ADD SIGNATURE TO dbo.Test
BY CERTIFICATE cert_Test;
GO
-- Remove the private key
ALTER CERTIFICATE cert_Test
REMOVE PRIVATE KEY;
GO
-- Get the certificate in binary form
SELECT CERTENCODED(CERT_ID('cert_Test'));
GO
 
USE master;
GO
-- Create the same certificate
-- Use the binary from CERTENCODED output
CREATE CERTIFICATE cert_Test
FROM BINARY = 0x308201B930820122A0030201020210683833AF801CBEB44E3CCBEC95F4EF39300D06092A864886F70D0101050500301B311930170603550403131054657374206365727469666963617465301E170D3133303232383132343930305A170D3134303232383132343930305A301B31193017060355040313105465737420636572746966696361746530819F300D06092A864886F70D010101050003818D0030818902818100C51D69634A2800D9F6C7BF3596ADA009129ECBCB5D959C854E722C14878972A93CF30D6AD07FF73BF2E1035CC702EC25404FE5ADC5756D247FA682E6AD9A086E26214234B0CC6D7D35D139EF8130D4A8EEA45A15000DA386433B1FCC641688211A8279A133DB334DDD27A568770C984A07B0DED92B9AF7FF6E5CD8287EE24D290203010001300D06092A864886F70D010105050003818100481DFEACEDC9A8ADBDA3DA6AB81D8F00F9B5A409D0CD6EA396ADBAF0258F2C5547BF03D60A56A1FA1B7FCD78D49FC04D43D8D44078EF21021CBF72DA07636FDCF67D064A77BD516E4306FFD2BFA50BBCF76085A934992F81DB94E883F8009E7B36292F15A58D4E62A6999CCCA4BCC03D1E544B2A1C3702E76671E50A7D63C0CB;
GO
-- Associate a login with the certificate
CREATE LOGIN cert_Test
FROM CERTIFICATE cert_Test;
GO
-- Ensure login cannot connect to SQL Server
REVOKE CONNECT SQL FROM cert_Test;
GO
-- Add the login to sysadmin
ALTER SERVER ROLE sysadmin
ADD MEMBER cert_Test;
GO
-- Create a real login to test the signing arrangements
CREATE LOGIN TestUser 
WITH PASSWORD = 'password', DEFAULT_DATABASE = Banana;
GO
USE Banana;
GO
-- Create real test user and grant execute permission on the procedure
CREATE USER TestUser FROM LOGIN TestUser;
GRANT EXECUTE ON OBJECT::Test TO TestUser;
 
-- Now login as TestUser and execute the test procedure
 
-- Clean up
USE Banana;
GO
DROP USER TestUser;
DROP PROCEDURE dbo.Test;
DROP CERTIFICATE cert_Test;
GO
USE master;
GO
DROP LOGIN TestUser;
DROP LOGIN cert_Test;
DROP CERTIFICATE cert_Test;
DROP DATABASE Banana;
GokhanVarol 2013-02-28 15:19:29
Worked like a charm, Thank you
February 28, 2013
No Comments