0

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 permissions 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.

Andre
  • 1,852
  • 1
  • 16
  • 21

1 Answers1

1

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).

Dan
  • 1,927
  • 2
  • 24
  • 35