I createdÃÂ an application role in a database (DB1) and gave it all the rights on a viewÃÂ in DB1 which refers to a table located in another db (DB2).ÃÂ I also gave the rights to the app role on a table ofÃÂ DB1
I tried to use this app. role through the sp_setapprole launched by a userÃÂ (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:
The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?
The table in DB2ÃÂ has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.
Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
View Complete Post