Is it possible to grant rights to DBCC TraceOn to a user
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
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