Msg 916, Level 14, State 1, Procedure ViewClients, Line 3
The server principal "Login2" is not able to access the database "Blog" under the current security context.
We have a web application, in two different web servers, pointing to the same SQL Server, but each app to a different DB in the same instance. The tricky part here is that DB B is built on views of DB A, so there are no "real" tables, but only views.
Programmatically, we use SPs to access the data, so the SPs in DB B execute selects on the views on DB B, that are created from DB A
So, for integrated security, we wanted to create two different logins, each one with a different user to each DB:
Web Server A -> Login A -> User A -> DB A
Web Server B -> Login B -> User B -> DB B
The problem arouse the moment we wanted to access the app in Web Server B, we received the error message above mentioned.
The first option was to create the User B in the DB A, and giving the same permissions than the User A. But this way we were allowing access and select permissions to User B in the DB A, and this was not what we wanted. So what to do? After doing a little research, I came up with Cross Database Ownership Chaining (which you can check in the excellent entry). Briefly, with this we could avoid giving User B permissions on DB A, and at the same time allowing the SPs and views in DB B access the data in DB A. After that, we could execute the SPs in DB B with no problems.
To check if a DB has the chaining option enabled, you can use this query:
select name, is_db_chaining_on from sys.databases where name in (dbname here)Here is a fully functional code example of the analyzed scenario
Of course, if misused, this option could lead to security holes, so don't enable it so fast. Here is an article about possible dangers of enabling Cross-Database Chaining
