locked
Permissions for Opening Trace Files RRS feed

  • Question

  • I am running into a road when trying to allow a user to view trace files.  Our current process is to capture trace files on a given server and write it to a maintenance server.  

    The user in question has sysadmin on the database/server where the trace files are being captured.  The user also has ALTER TRACE, VIEW SERVER STATE permissions on the server where the trace files reside (the maintenance server).  

    Speaking to the file system:  The user has full admin access to the maintenance server.  

    The problem:  The user is unable to open any trace files on the maintenance server and gets, 'Failed to open a file.  Access is denied.'  

    The user is also unable to change the permissions for the individual trace files themselves even though they are admin on the filesystem.  

    Does the user also need to be a securityadmin on the maintenance server?  I am at a loss at this point how to set permissions.  

    Note:  Moving the trace files to another folder once they are on the maintenance server allows the user to access the files themselves but this an unacceptable workaround since it will require me to move the trace files each time the user needs to analyze them.  

    Tuesday, April 15, 2014 6:25 PM

Answers

  • So in order to solve this it was based on Windows permissions but it was also very strange...

    The user was in a group that was listed in Administrators on the box.  Even with this the user did not have admin rights on the box itself...

    In order to solve this we had to give the user explicit Windows admin rights but I find it weird that we had to do this.  

    The Windows problem is most likely related to UAC.  The Administrator group membership is not fully effective unless Profiler is launched with Run as Administrator.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Sofiya Li Tuesday, April 29, 2014 3:43 AM
    Friday, April 18, 2014 2:49 PM
  • Since yesterday I tried giving the user full sysadmin on both servers to test it and they are still unable to open the file using profiler.  The trace job is owned by disabled SA if that matters.  

    Again, to open a file in Windows, you need permissions in Windows. They may be sysadmin in SQL Server all day long, but that is not going to help. Right-click the file, select Properties and the go the Security tab and check Windows permissions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sofiya Li Tuesday, April 29, 2014 3:43 AM
    Wednesday, April 16, 2014 9:47 PM

All replies

  • you can refer the below link

    http://blogs.msdn.com/b/varund/archive/2009/06/26/error-failed-to-open-a-file-access-is-denied-when-attempting-to-open-sql-2005-profiler-trace.aspx

    -Prashanth

    Tuesday, April 15, 2014 6:40 PM
  • This is not the answer in this case:

    'This is strange, since there another SQL 2000 Instance, where he can successfully open the trace files. He has NO access on both SQL 2005 and SQL 2000 instances. '

    For the case I detailed the user in question has full sysadmin on the box where the trace is being captured.  The user also has admin on the folders where the trace files are being written to.  

    He also only proposes a work-around which is not acceptable.  Moving trace files to another folder is circuitous and cannot be the answer.  

    Tuesday, April 15, 2014 7:23 PM
  • If a user gets "Access is Denied" when trying to access a file in Windows, there are two possible reasons:

    1) He does not have permissions in the file system to access the file.
    2) The file is exclusively locked by another process.

    On the other hand, permissions in SQL Server have zero to do with it.

    How to the users try to access the file? Profiler or fn_trace_gettable()?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Wednesday, April 16, 2014 8:33 AM
    Tuesday, April 15, 2014 9:53 PM
  • The user is trying to open it with Profiler on the server the file resides.  

    Since yesterday I tried giving the user full sysadmin on both servers to test it and they are still unable to open the file using profiler.  The trace job is owned by disabled SA if that matters.  

    Wednesday, April 16, 2014 2:52 PM
  • Since yesterday I tried giving the user full sysadmin on both servers to test it and they are still unable to open the file using profiler.  The trace job is owned by disabled SA if that matters.  

    Again, to open a file in Windows, you need permissions in Windows. They may be sysadmin in SQL Server all day long, but that is not going to help. Right-click the file, select Properties and the go the Security tab and check Windows permissions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sofiya Li Tuesday, April 29, 2014 3:43 AM
    Wednesday, April 16, 2014 9:47 PM
  • So in order to solve this it was based on Windows permissions but it was also very strange...

    The user was in a group that was listed in Administrators on the box.  Even with this the user did not have admin rights on the box itself...

    In order to solve this we had to give the user explicit Windows admin rights but I find it weird that we had to do this.  

    Either way this is now solved.  

    Friday, April 18, 2014 1:24 PM
  • So in order to solve this it was based on Windows permissions but it was also very strange...

    The user was in a group that was listed in Administrators on the box.  Even with this the user did not have admin rights on the box itself...

    In order to solve this we had to give the user explicit Windows admin rights but I find it weird that we had to do this.  

    The Windows problem is most likely related to UAC.  The Administrator group membership is not fully effective unless Profiler is launched with Run as Administrator.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Sofiya Li Tuesday, April 29, 2014 3:43 AM
    Friday, April 18, 2014 2:49 PM