You mean, calling a stored proc under a given user? Yes, in adapter, specify a connection string with a given user, and pass that adapter to the method to call the proc.
With a dyn. sql query this isn't possible: the tables accessed by the query running under user X have to be accessible (select right) by user X. You can use roles to limit this though.
The proc P can access a lot of tables but the user executing P only has to have access to P, not to the tables.
This is at first nice and 'more secure'. However it's a bit misleading. The proc api allows me to execute all actions by simply passing input, I can still access the data through the proc api. I just have to sniff connection strings over the network or in the app code and I can do what the api offers me.
Therefore if you want to secure a proc API, always make sure the caller has to pass a security token, only known at runtime to the proc, to verify the caller is indeed allowed to do what is supposed to be done.
So dyn. sql queries do indeed require table-wide permissions. To mitigate this:
- use views, accessible by specific users so the tables don't have to have access rights
- use different users for CUD and selects
- use table valued functions (which offer the security of procs, but you can use them in an ORM)