I am on the look out for some suggestions on how to get it working. Let me explain you what our config looks like, so we have 2 databases one which has some base tables in dbo schema let's say db A and have like x no. of tables that are used by 3 views in another db let's say B and these 3 views are in a different schema have underlying triggers that would be used to perform DML operations on these base tables. Now, the requirement is to have a specific login for e.g domain\xyz-advanced which would have access to the db B on these views to perform DML operations but it won't have any access as such to view or do anything in db A. Just fyi...I've enabled DB chaining on both the DBs and mapped the login account as a public user on db A as a criteria for DB Chaining. However, when I am trying to use the login in order to select through the views it throws me an error as below.
The server prinicpal domain\xyz-advanced is not able to access the database A under the current security context.
I've pretty much looked into the DB properties or anywhere that would cause an explicit revoke or deny but didn't find anything out of ordinary...so I am not sure why it isn't working.