0

In my Azure SQL managed instance I have two databases, Database_A and Database_B. User A_ABC has access only to Database_A and he is part of role db_owner.

When the tries to alter a table we are getting the below error

Query: alter table mta.mta_dmn add active char(3)

Error: Msg 916, Level 14, State 2, Procedure TR_XYZ_DDL_AUDIT, Line 8 [Batch Start Line 0] The server principal "A_ABC" is not able to access the database "Database_B" under the current security context.

There is no cross database joins or any link between Database_A and Database_B, with Database owner permission my understanding was the user should be able to do any activity within the Database_A. Also there is no procedure 'TR_XYZ_DDL_AUDIT' which exists in either of the databases

I am not able to find why this error is coming/not able to understand this error or able to find a solution. Dropped the login completely and recreated just in case, but the issue persists

1 Answer 1

3

My guess is that you have a DDL trigger, which is the root cause for this. Check for triggers in Database_A (assuming this is the the database where the table to be altered lives).

Possibly this trigger executes a stored procedure in Database_B which should do some auditing of this operation.

The name TR_XYZ_DDL_AUDIT, gives us some clues.

1
  • Kazari yeah you are right, this trigger was at sever level which was for some auditing purpose. Disabling it resolved this issue. Thanks
    – Venkat
    Mar 5, 2020 at 10:28

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.