SYS ADMIN privileges needed to modify AutoAudited Tables.

Mar 28, 2013 at 2:34 PM
Edited Mar 28, 2013 at 5:01 PM
First off, AutoAudit is an excellent tool! I'm the process of designing a "database first" web app, and I have several dbos making changes simultaneously. Rapid development. AutoAudit lets me know when/what db schema changes are happening so that I can adjust my app accordingly. Perfect!

My issues revolves around the fact that my dbos do not have SYS ADMIN privileges. So, after I wired up a table for auditing, it also prevented my dbos from modifying columns in their tables via SSMS. They were greeted with this error message.

Image
"Only System Administrator can specify WITH LOG option for RAISERROR command"

The issue stems from the fact that sometimes the default behavior of the SSMS visual table-editor is to drop and recreate a table on save. This "table recreation upon save" includes recreating the AutoAudit triggers applied to the table. The triggers make use of the server-level WITH LOG option. Because my dbos don't have server-wide permissions, they can't recreate the triggers. Or atleast, that's how I interpret it.

This "bug" obviously only impacts a subset of SSMS users which are editing tables via the designer and don't have server-wide permissions, but it is common enough, that it may be worthwhile to include in the documentation.

The Solution(s)
  1. Grant your dbo ALTER TRACE permissions. Like so.
  2. Elevate your dbo to sys admin.
  3. Have your dbo use ALTER TABLE commands instead of SSMS's visual table manager.
Hope this helps someone else out!
  • Troy Witthoeft
Coordinator
Oct 31, 2013 at 4:52 PM
Edited Oct 31, 2013 at 4:53 PM
This was a problem in previous versions of AutoAudit.
With the latest release, there is an option to toggle the "With Log" option when errors are raised.
Near the top of the installation script (about 35 lines from the top) set the @WithLogFlag = 0 to prevent this problem. In fact, this is the default setting for version 3.20e

from the setup script....

Set @WithLogFlag = 0
--This flag determines if the "With Log" function is included in the raiserror statements or not
--this is added because some DBA's may not have rights to write to the Windows log
--0 = exclude "with log", 1 = include "with log"
Marked as answer by JohnSigouin on 10/31/2013 at 9:53 AM
Dec 5, 2013 at 5:44 PM
Nice! So glad to see this made it into the default settings for the future versions.
Thank you very much!