I have a case where I need to create a view in DB1 that points to base tables in DB2. The user only has access to the views in DB1 and doesn't have any access within DB2.
What rights or permission do I need grant to the user or view in DB1 to be able to see the data defined within the view.
If the User in DB1 has rights to the View then it can use the resources that the View is dependent on. What is needed, in order to cross the inter-database gap is for cross database ownership chaining to be enabled. This can be done at the server level:exec sp_configure 'cross db ownership chaining', 1;
reconfigure;
or the database level: alter database MyDB DB_CHAINING ON;I could be wrong but I think you'd need this set on both DBs.
From what I have read about cross database chaining, is that it doesn't work for view or dynamic SQL. It only works for sprocs.
Cross-database ownership chaining does not work in cases where dynamically created SQL statements are executed unless the same user exists in both databases
create a user (or use one already there) who has select on DB2. Set trustworthy on for both databases. Make sa the owner of both databases. Add the user to DB1. Grant the user in DB1 impersonate on the user in DB2;
Thank you, that worked. Thanks for the help.