I have a single instance of SQL 2008R2. I have two seperate databases...one ERP the other a CRM. I have users who login to the ERP program and users who login to the CRM program. I've created a view in the ERP database the queries a table in the CRM database. And of course not all users in the ERP database have rights to the CRM database and when they run a report from the ERP system that uses that query they will get the following error.
The server principal "username" is not able to access the database "CRM" under the current security context.
I don't want to have to manage each user of the ERP database and give them rights to the CRM database. So, I've been searching for a way to be able to run the report/query. Here's what I have to work with...but still not working.
The ERP database has a database role that every user belongs to.
The SQL public server role has been granted 'select' rights to all the tables in CRM.
I know I can get it to work if I grant each user of the ERP database rights to the CRM database, but I was hoping I could do it without having to touch each user of the ERP system...as the accounts are being added/removed often.
Any insight would be greatly appreciated!