0

I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error.

database "XXX" failed on Jan 17 2019 10:36PM with error #297, severity 16", state 1 in procedure/trigger "DDLTrigger_Audit" , line # 29 while processing object "sp_XXX" with the following message: The user does not have permission to perform this action.

I have tried giving GRANT CONTROL SERVER permissions to the account executing the trigger and tried running the Stored procedure again. I got a different error this time.

The statement has been terminated. Msg 8152, Level 16, State 14, Procedure sp_XXX, Line 182 [Batch Start Line 2] String or binary data would be truncated. Mail (Id: 6500) queued.

I am confused if the issue is with a stored procedure or with the trigger.

  • Drop and Create index statements cannot produce "String or binary data would be truncated". It's your sp (line 182) that tries to insert a large value that does not fit in some field – sepupic Jan 18 at 7:07
  • By granting the permission, you solved the first error. Grating control server is of course brutal, you could grant permissions on the table that the index is created on, for example, However, then you ran into a second problem, not related to the first problem, the truncation - which you need to identify and fix. – Tibor Karaszi Jan 18 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.