Permission to run SP_TRACE_SETSTATUS

Ver Argulla J

Permission to run SP_TRACE_SETSTATUS

by Ver Argulla J » Tue, 25 Dec 2007 14:38:09

Hi,

I'm trying to execute the following line:

EXEC

but I get the following error:

You do not have permission to run 'SP_TRACE_SETSTATUS'.

I am running under an administrator account and the ALTER TRACE permission has been granted on this as well. I even tried the sa account but to no avail. Any help would be appreciated!

Thanks,

Ver



Dale

Permission to run SP_TRACE_SETSTATUS

by Dale » Thu, 27 Dec 2007 15:39:10

In SS2000 you must be a member of the sysadmin role.

In SS2005 you need ALTER TRACE permission.

If you have those and it's not working...something's fishy.



Beth6

Permission to run SP_TRACE_SETSTATUS

by Beth6 » Fri, 28 Dec 2007 17:41:12

After additional research I have found that the C2 Audit was enabled and running causing the trace files to fill up the data area. I'm not sure why the C2 Audit was turned on, but it appears to have occurred during an upgrade to SP2 back in March. In order to disable this trace, I ran the following sp_configure procedure and restarted SQL Service, my guess is that it should also work by running reconfigure with override as well.

Exec sp_configure 'c2 audit mode', 0;

The C2 Audit must be programmed in such a way that it cannot be modified using the sp_trace_setstatus procedure and returns the somewhat erroneous "No Permission" error.




Beth6

Permission to run SP_TRACE_SETSTATUS

by Beth6 » Sat, 29 Dec 2007 16:40:11

I'm having a similar problem and there is something fishy as the SP_TRACE_SETSTATUS procedure returns a completly different error when you modify the status to 1 or 2. I don't think it's a permission error, though that is the error that is being returned. Is there some other way to stop a trace, if we cannot access it through this procedure



Ver Argulla J

Permission to run SP_TRACE_SETSTATUS

by Ver Argulla J » Sun, 30 Dec 2007 18:42:13

I get the following message after running the command:

The configuration option 'c2 audit mode' does not exist, or it may be an advanced option.


Ver Argulla J

Permission to run SP_TRACE_SETSTATUS

by Ver Argulla J » Mon, 31 Dec 2007 20:44:15

Hi Beth,

Thanks a lot for this. I have ran all the commands successfully. Hope this is the solution to the problem. Again, thanks.

-Ver

Beth6

Permission to run SP_TRACE_SETSTATUS

by Beth6 » Tue, 01 Jan 2008 19:43:14

Set the 'show advanced option' to '1'

USE master;
GO
EXEC sp_configure 'show advanced option', '1';

Then run reconfigure:

RECONFIGURE;

Then show all configuration options;

EXEC sp_configure;

Verify that the 'C2 Audit Mode' config_value and run_value fields are set to '1'. If they are - that is what needs to be changed. You should be able to run the code to set the config_value to '0' (disabled).

EXEC sp_configure 'c2 audit mode', '0';

If you run EXEC sp_configure; again it should show the config_value is '0' and the run_value is still '1'. To turn off the c2 audit trace, you need to have the run_value set to '0' as well. To get this, run:

EXEC RECONFIGURE with OVERRIDE;

If that doesn't do it, then restart the SQL SERVER and SQL AGENT services and the config_value will get picked up.