February 14, 2010 at 3:52 pm
Hi All.
I have a situation where an application uses a 3rd party API to run Crystal Report reports against Database 1. Their access is via a web site and the webuser has access via a DB role and all works well.
I need to provide access to data from DB2 so I created views in DB1 to retrieve data from DB2. They work well for the sa account but not for the DB role the web site uses.
Both DBs are located on the same server. I am under the impression that queries inside a view or stored procdure run as dbo and only the SP/view security limits data access. It seems I am wrong. Both databases are owned by dbo/sa.
What do I need to do to provide access to DB2 to the web site login?
Is there any way I can trace the security or permissions from the user to the db?
Many thanks
Greg
February 16, 2010 at 6:36 am
See if this helps
http://qa.sqlservercentral.com/articles/Advanced/designingcrossdatabasequeries/1753/
(see the "security" section).
Enable the "db" role to log in to DB2 and grant the necessary rights to the base table(s).
To list permissions for a principal on a securable use "fn_my_permissions".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply