Why does this stored procedure execute when an user does ALTER USER .... DISABLE?-CodePudding

A specific user - Main_user (with a default database of db_A) executes

USE db_A
ALTER USER another_user DISABLE;

Every time it tries to do so, an error message is shown:

Msg 916, Level 14, State 2, Server ****, Procedure *****sysadmin, Line 32
The server principal Main_user is not able to access the database db_ZZZ under the current security context.

As you can see, it seems that the procedure tries to execute code that involves db_ZZZ, which apparently has nothing to do with the ALTER USER statement.

The thing is that I cannot find the procedure ******sysadmin anywhere (not a trigger, nothing). And I can't understand what is the business of it in all this situation.

What explanations could be plausible?

I don't know if it could be useful to mention that Main_user is a service user of Wherescape RED. I cannot test this code with any other user because I don't have any other with permission to do so.

CodePudding user response：

Like Larnu said. You can find the trigger using this query:

SELECT *
FROM   sys.triggers AS t
       JOIN sys.all_sql_modules AS asm ON asm.object_id = t.object_id
WHERE  asm.definition LIKE '%*****sysadmin%';

CodePudding user response：

It sounds like you have a DDL trigger enabled on your server. To see if that's the case, look in the sys.server_triggers system view or, in SSMS, look under Server Objects → Triggers. From there, you should be able to see what the trigger's definition is and make a decision as to whether you want to keep it (with modifications so that it actually works) or drop it altogether.