User does not have permission to run DBCC TRACEON

I have a huge database build under Microsoft SQL Server 2005. I recently faced a major problem when running certain queries in it. Whenever I run a query to get connected and retrieve data from the database, I have the following error message:

Quote:

---

[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON.

---

What is this message about? Is it possible for me to grant access to run DBCC TRACEON to a specific user? How?

Are you trying to set a traceflag when retrieving the data? If so, then I suppose you don't need to trace flag. Actually, to set the trace flags you need to be a sysadmin privileges. So it certainly is not possible. If you really want to do so then use some different method for getting data.

It is the problem of application name which you may have used. To resolve this problem, just change the name of the application for the ODBC connection. Ideally the name used should be other than 'Microsoft Query'!

To run DBCC Traceon, you require sysadmin rights. You can also programmatically change the connection string using the below example:

Code:

---

'ChangeConnection
Sub ChangeConnection()
    Dim sh As Worksheet
    Dim qt As QueryTable
    Dim sConnection As String
    For Each sh In ActiveWorkbook.Sheets
        For Each qt In sh.QueryTables
            'Show current connectionstring
            MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: " & vbCr & qt.Connection)
            'Show current query
            MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr & qt.CommandText)
            'Change Connection
            qt.Connection = "ODBC;DRIVER=SQL Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"
            'Change Qry text (the owner for instance)
            qt.CommandText = Replace(qt.CommandText, "DB.dbo.", "DB.Me")
            qt.SavePassword = False
            'Show new connectionstring
            MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr & qt.Connection)
            'Show new query
            MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr & qt.CommandText)
        Next qt
    Next sh
End Sub

---