permission problem on dynamic query running in a stored procedure

i have this stored procedure, doing a select query with couple of inner joins (one of the tables is in another db). Now, i had to write this query as dynamic cause first i had to find which db the select query should run. Anyway, none of the tables have permissions on them, just giving permission to the stored procedure for the database role "personel" (which includes everyone).

Bu开发者_运维技巧t now, someone with a personel role runs this stored proc, they are getting the error "The SELECT permission was denied on the object 'tbl_table', database 'Db', schema 'dbo'." no difference in the schema, and there are other procs using the same table that are running normally.

Can using a dynamic query (exec (Use DB; select ...) ) be the reason for this? Like cause it is dynamic, i should give permissions to the tables also ?

Thanks

---

The short answer is yes.

When you compile a stored procedure, permissions of the user/login creating the stored procedure are checked. When someone else executes it, their ability to read those tables is no longer relevant (in most cases), but rather just their ability to execute the SP.

When executing the dynamic code, however, the permissions regarding the tables have to be checked there and then. This means that the executing user's permissions are being checked.

---

Yes, this can be the reason. Read this to get an explanation and a possible solution.