sql server - SQL owner chaining -

basically have front end application called adminprogram. in sql server have role called adminprogramusers.

now role has permissions various sps , but, crucially, there no select permission on table. see or modify done through sps.

now i've written new sp them. have had no choice use dynamic sql, constructing actual sql query in string variable called @finalquery , running execute @finalquery.

now getting lot of user doesn't have access tables, needed either grant selects columns in question, solution won't work due internal politics.

the other thing seem understand need use execute as somehow. have googled, i'm struggling right.

and pre-emptively answer couple of questions, there no option use dynamic sql in sp displeasure, we're talking sql server 2005 , there no way of signing sps certificates (mainly dba flip nut @ prospect of complicated).

so... think answer lies somewhere in execute ownership chaining... need know how it... assuming role available adminprogramusers...

the current solution have is:

create sp mysp declare @finaltable (columns)  declare @finalquery set @finalquery = "execute caller select blah blah"  insert  @finaltable  execute (@querystring) user ='adminprogramusers'  

do more processing on @finaltable

select * @finaltable 

the error is:

unexpected error in sp cannot execute database principal because principal "adminprogramusers" not exist, type of principal cannot impersonated, or not have permission.

here excerpt ms documentation execute on sql server 2005:

the user or login name specified in { login | user } = ' name ' must exist principal in sys.database_principals or sys.server_principals, respectively, or statement fail. additionally, impersonate permissions must granted on principal. unless caller database owner or member of sysadmin fixed server role, principal must exist when user accessing database or instance of sql server through windows group membership.

in code feed execute role user or login expected instead.

the above text not clear if don't read this:

login

specifies context impersonated login. scope of impersonation server.

user

specifies context impersonated user in current database. scope of impersonation restricted current database. context switch database user not inherit server-level permissions of user.

you can specify user or login impersonation context; a role neither login nor user.