How do I write to an external audit database in SQL Server 2008?

I have a SQL Server 2008 database with 10 windows users who all have permissions to Insert, Update and Delete tables. Each table has a trigger that writes to an audit table in a different database.

Currently for this to work I have to give the user write permission开发者_如何学JAVAs to the audit database as well, otherwise the trigger will throw an error.

I could give Insert permission only for each individual user, but I was hoping that there might be a more elegant solution for this problem. Especially from a standpoint that users get deleted/added which would mean setting them up in two databases rather than one.

Ideally I would like to use one account that does all the audit work.

When you create a trigger you have the option to specify credentials: see "Execute As" (http://msdn.microsoft.com/en-us/library/ms189799.aspx and http://msdn.microsoft.com/en-us/library/ms188354.aspx).