Sql-server – Trace and analyze logs on SQL Server

1. You'd have to substitute the datetime in the sp_trace_create
   That is, drop and create each day unfortunately (as you noted). There is no "alter trace"

2. Overwritten. The 1, 2, etc suffix is for rollover: not for trace startup

3. It isn't locked.
   The NTFS ownership and permissions are not inherited from the parent folder. You need to change this using Explorer or via scripting (cacls, subinacl) (not sure exactly sorry, been some time)

I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to potentially outpace your actual data. Might want a separate database on isolated storage for that.

Auditing, CDC and Change Tracking would be great if they were combined, but they're not. As you've discovered, auditing shows parameter placeholders, so it tells you who changed it but not what they changed, and the other two tell you what has changed, but not who changed it or when.

I've asked for the ability to include username and date/time in CDC data, but it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

I also asked for date/time information to be included in Change Tracking, but similarly it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/319049/change-tracking-provide-datetime-column

I wasn't aware of the auditing limitation. If I had known about it, I would have asked for it, too. And it would have been declined like the others.