Results 1 to 4 of 4

Thread: User does not have permission to run DBCC TRACEON

  1. #1
    Join Date
    May 2008
    Posts
    860

    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:
    [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?

  2. #2
    Join Date
    May 2008
    Posts
    685

    Re: User does not have permission to run DBCC TRACEON

    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.

  3. #3
    Join Date
    Jan 2008
    Posts
    1,521

    Re: User does not have permission to run DBCC TRACEON

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

  4. #4
    Join Date
    May 2008
    Posts
    271

    Re: User does not have permission to run DBCC TRACEON

    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

Similar Threads

  1. Change the permission level of a user in Frontpage
    By D_chapple in forum Tips & Tweaks
    Replies: 1
    Last Post: 06-02-2011, 03:44 AM
  2. Making permission for normal user in Linux
    By Disha N in forum Operating Systems
    Replies: 4
    Last Post: 27-12-2010, 09:56 AM
  3. Mstsc /console user permission
    By Itkila in forum Active Directory
    Replies: 2
    Last Post: 06-01-2010, 03:29 PM
  4. Permission for user
    By Asghar in forum Operating Systems
    Replies: 1
    Last Post: 16-10-2009, 11:13 AM
  5. Display the user permission on a share folder
    By Klums in forum Active Directory
    Replies: 3
    Last Post: 12-08-2008, 08:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •