You do not have permission to run 'SP_TRACE_SETSTATUS'

Question

Hi Friends,

I am trying to stop and disable a trace using "exec sp_trace_setstatus 1,0 "

command. But it gives the following error  "You do not have permission to run 'SP_TRACE_SETSTATUS'". I am using an administrator account. I also tried sa login which gives me the same error. Need Urgent help.

Any help an suggestions are greatly appreciated!

Thanks,

Gopu

---

Gopal

Answer 1

It's not a permissions issue in the normal sense -- trace 1 is the default trace, which you can't stop using this stored procedure. If you're on SQL 2005 or later do a SELECT * FROM sys.traces and get the trace id for the one you started. You can also look in the SQL log and see the trace id there.

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you.

Answer 2

Hi,

Why do you want to stop the default trace? It is very useful and has little to no impact on the performance of the system.

Which version are you using? If I had to I would use sp_configure to disable the default trace in 2008 and up. Is c2 audit mode enabled? If is it running it may cause you not to be able to stop the default trace I still don't know why you would want to as it is great during troubleshooting.

http://msdn.microsoft.com/en-us/library/ms175513(v=sql.90).aspx

http://msdn.microsoft.com/en-us/library/ms189631(v=sql.90).aspx

exec sp_configure 'show_advanced_options',1
go
reconfigure
go

exec sp_configure
go

exec sp_configure 'default_trace_enabled',0
go
reconfigure
go

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Thank you! for the reply Friends!

I execute this command to list all the traces on the server

select * from ::fn_trace_getinfo(default)

It listed two traces with trace id's 1 and 2.

So I ran your piece of code and to confirm I again listed the traces. I saw trace id 2 was gone this time. So Trace id 1 is not default.

Need help..This trace is create trace files which is filling up C;\ Drive very fast. I am manually deleting all the file to keep the disk free.

Thanks,

Gopu

---

Gopal

Answer 4

I execute this command to list all the traces on the server

select * from ::fn_trace_getinfo(default)

It listed two traces with trace id's 1 and 2.

So I ran your piece of code and to confirm I again listed the traces. I saw trace id 2 was gone this time. So Trace id 1 is not default.

The default trace is typically small.  It also has a rollover file specification to limit drive space used.  I suggest to re-enable the default trace and concentrate on the other trace as the culprit (exec sp_trace_setstatus 2,0).  I agree with Matt that you should use sys.traces instead of fn_trace_getinfo if you are using SQL 2005 or above.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Re-enable the default trace and find out who enabled C2 auditing and why? When you enable the auditing at the instance level it makes the default trace id # 2 and you get the permissions error when you try to stop the trace ID # 1 as this has been enabled for auditing therefore they don't want someone to be able to stop that trace and therefore hide what they do from the audit trail.

/*------------------------
exec sp_trace_setstatus 1,0
------------------------*/
Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 1
You do not have permission to run 'SP_TRACE_SETSTATUS'.

You can see here the trace id # 1 is now the audit trace.

select id, status, path from sys.traces
------------------------*/
id          status      path
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1           \\?\F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2B\MSSQL\DATA\audittrace20120413121903.trc
2           1           F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2B\MSSQL\Log\log_10.trc

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!