SQL Server Trace Not Inherit Permission - Solution

If you stumble upon this post because you are trying to find out what went wrong on your SQL Server trace permission created using sp_trace_create and it is not inheriting NTFS permission from the parent folder, this post may provide you a solution. This is actually a known issue for Microsoft. You can find the ticket submitted here.

Microsoft refuse to fix the problem since trace has been deprecated in SQL 2012 and they wants to maintain the backward compatibility from the security aspect. To address the problem, they suggests to replace trace with extended event which will inherit parent permission.

But if you are stuck with SQL Server trace and need to continue using it, here is a get around temporary solution. You can use Calcs command to modify the NTFS permission right after you create the trace.

calcs FileName [/t] [/e] [/c] [/g user:permission] [/r user[...]] [/p user:permission[...]] [/d user[...]]

FileName : Displays DACLs of specified files
/e : Editing permission instead of replacing
/g user:permission : Grant access right to specified user with this values

Permission
n - None
r - Read
w - Write
c - Change (Write)
f - Full Control

More details on calcs and its parameters can be found here.

Create a job with first step creating SQL Server trace, and add an operating system (CmdExec) job step (only members of sysadmin can create CmdExec job step) to change the permission right after the trace has created. Below is an example of the command. (granting read permission for mydomain/testuser for trace1 file located at testfolder on the network drive.

CACLS "\\testfolder\trace1.trc" /e /g mydomain\testuser:r

Alternatively, if your environment allows xp_cmdshell, you can execute the calcs command through xp_cmdshell as well.