Error “Failed to open a file. Access is denied”, when attempting to open SQL 2005 Profiler Trace

  • Article

Hello All,

Yesterday while doing the regular stuff, I was reported a strange error related to profiler traces. I broke my head for a day almost to figure out what was happening.

Issue: I have a SQL Agent Job that creates profiler trace files using sp_trace_create. The job is being owned by my ‘Domain’ A/c which is also SQL Service start-up account.

My colleague (say user-X), when attempts to open the trace files gets error >>

“Failed to open a file. Access is denied”.

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. After several hours of agonizing and troubleshooting, I finally got this on CSS Blog >> How It Works: Trace (.TRC) File Security

“SQL Server 2005 has a different trace file security as compared to previous build of SQL 2000. Per new security design.The owner of the trace files must explicitly grant security permissions to others in accordance with security policies and company guidelines”

So a short solution to this is to grant user-X access to SQL Server.

However, what if I don’t want to have user-X any access on SQL Server. Here’s the workaround I tried and it worked perfect for me:

  • Use NTFS Permissions Inheritance in Windows

- Create a shared-folder where both Job-owner and user-X have full access

- Job-owner copies the trace files and paste them to above shared-folder

- Now, user-X will be able to open the trace files without any issue, since the parent folder's permissions are inherited by all files of the parent.

Refer to Microsoft KB Control NTFS Permissions Inheritance in Windows for more details.

If you happen to think a better workaround or a different idea - I'm all ears.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.