• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

How do you change the default username for default SQL Trace

I noticed in the sql logs that in this entry -

SQL trace ID 1 was started by login "sa"

How do I change the user or login for the default sql trace getting automatically started?
Meaning, I don't want the instance using the user = 'sa' to login, etc.

1 Solution
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Default SQL Trace is enabled in SQL Server by default to capture many useful information listed below:


To capture all this information, it should / would ideally use the sa privileges and hence it is configured to run using sa credentials.

We have the option to either disable it but not change the default user for it.

Steps to disable the trace( Not recommended):

sp_configure 'default trace enabled' , 0

I agrred with the rrjegan17 cooment. It is right that you can off trace but can not change the login.

Default Trace in SQL Server 2005
When i saw *SQL Trace ID 1 was started by login "sa" * in my Error Log I was little surprised. Since i have never started any trace I was wondering what is this all about. Then i came to know that this is the default trace started which is a new feature introduced in SQL Server 2005. Then i was curious about how can i see the contents. here we go...

To See All the Trace and its location :
select * from ::fn_trace_getinfo(default)

To get the File Location
select * from ::fn_trace_getinfo(default) where Property=2

And copy paste the Value column text.

To Get the Trace Data

SELECT * FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default) where starttime>'2009-12-26'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now