?
Solved

How do you change the default username for default SQL Trace

Posted on 2009-12-23
2
Medium Priority
?
573 Views
Last Modified: 2012-05-08
Hello,
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.

Thanks
0
Comment
Question by:sahkeah
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26117359
Default SQL Trace is enabled in SQL Server by default to capture many useful information listed below:

http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

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
GO
RECONFIGURE WITH OVERRIDE
GO
0
 
LVL 5

Expert Comment

by:rk_india1
ID: 26124207
Hi,

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'
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question