Re: ALTER TABLE TableName DISABLE TRIGGER error

Leonardo Arena (LeonardoArena@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I connect to MSSQL 2000 SP4 server through an Application Role and
execute a store procedure on DatabaseA

This first procedure calls another store procedure in a DatabaseB on the
same server.

All objects in the two databases have the same owner (dbo), and cross
database ownership chaining is enabled on both databases.

In the second procedure of DatabaseB, some insert, delete and update
statements are performed in a table MyTable residing on the same
DatabaseB.

Now the problem, in this store procedure, all insert, delete and update
statements work fine,

but when executing the command "ALTER TABLE MyTable DISABLE TRIGGER" an
error occurs: Error: 3704, Severity: 16, State: 1

We discovered that the ALTER TABLE command forces the audit of object
permission, and then the use of guest user instead of the application role
identity.

Yes, this is to be expected. The application role exists in database A,
and is only a user in this database. You call a procedure in database
A, and you are able to access objects in database A through ownership
chaining. That is, as long as the accessed objects have the same
owner as the procedure, the permission check is suppressed.

Then you call a procedure in database B. Since you have enabled cross-
database ownership chaining, and database B has the same owner as A,
ownership chaining is still in force.

Why then does the ALTER TABLE command fail? Because ownership chaining
only applies to INSERT, SELECT, DELETE, UPDATE and EXECUTE. (And possibly
things like WRITE/READ/UPDATETEXT.) It does not apply to DDL statements
like ALTER TABLE. Or for that matter TRUNCATE TABLE.

In SQL 2000 the actual user must have direct permission to execute these
commands, and in your case that means guest.

SQL 2005 offers other mechanisms to grant permissions through stored
procedures, but that is not likely to help you now.

Unless you want to cave in and give these permissions to guest, I think
you need to review your plan. Either abandon the application role, or
find a way to do this without disabling the trigger. A trick that I use
to disable parts of trigger code is that in the trigger I have something
like:

if object_id('tempdb..#trigger$disabled') IS NOT NULL
RETURN

The procedure that needs to disable this part of the trigger creates a
temp table with this name.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages