BAM Alerts and cross-database ownership chaining issue

BizTalk offers up many surprises to me even after about 8 years of constant experience with it.  Today’s lesson to me was a permission error we were getting in the BAMAlertsApplication stating that our SQL Notification Service user no longer had permissions to SELECT data from some of the tables.  I had never run into this before … ever … not in 8 years.

After much digging, we determined that starting the BAM notification server was calling a stored procedure in the BAMAlertsNSMain database: NSGetApplicationActivationStatus.  This stored procedure was, in turn, querying off tables in BAMAlertsApplication such as NSProcessActivationStates.  So why was this previously working but no longer?  We looked at permissions granted to this service account and it never had SELECT permissions on these tables, only EXEC rights to the stored proc.  From past experience, I knew there was some sort of “loop-hole” in which a stored procedure could return results from a table the user did not have permissions to.  This is, apparently, called ownership chaining and to make matters worse this was doing a cross-database join.  The setting allows this only if the table, stored procedure and databases are all owned by the same user.

After poking around awhile in the database and server options we found an option called: “Cross-database ownership chaining” which was turned off for the server as well as for these databases. Consulting another BizTalk setup that was working I noticed that this setting was turned on for these two databases but no others.  As luck would have it, the DBA had just detached and re-attached the database files to move them to a different location and a side-effect of this is that this one particular setting as noted here.  Seriously, this is the ONLY side-effect of re-attaching database files.  Crazy.

To fix this you can either grant the service account correct access to all the tables, etc., in the database or restore this database option.  Of course, it would have been better if Microsoft properly setup permissions on these databases in the first place.  Oh … and make sure you don’t change the database owner to an account that does not also own all the objects in these databases.