sql - Sqlserver stored procedure to execute DML on behalf of another user -

requirement:

user can log in ss instance, has access database x. user has no other access objects.

database x has stored_proc called "sp_exec_dml" takes dml string , executes it. stored proc would(should?) run owner x.

does database x, or stored procedure ""sp_exec_dml" need access other dbs/objects.

for example

user executes

exec x..sp_exec_dml n"update z..table set ..................."

i hope makes sense. come oracle background how permissions granted , grantees can confusing.

do databases or stored procedures have access granted them

thanks

according msdn : (url : https://msdn.microsoft.com/en-us/library/ms345484.aspx )

to grant permissions on stored procedure

in object explorer, connect instance of database engine , expand instance.  expand databases, expand database in procedure belongs, , expand programmability.  expand stored procedures, right-click procedure grant permissions on, , click properties.  stored procedure properties, select permissions page.  grant permissions user, database role, or application role, click search.  in select users or roles, click object types add or clear users , roles want.  click browse display list of users or roles. select users or roles whom permissions should granted.  in explicit permissions grid, select permissions grant specified user or role. description of permissions, 

see permissions (database engine).

selecting grant indicates grantee given specified permission. selecting grant indicates grantee able grant specified permission other principals.

this link explains few concepts : (url : https://dba.stackexchange.com/questions/6878/sql-server-stored-procedure-permissions )

stored procedures take advantage of ownership chaining provide access data users not need have explicit permission access database objects. ownership chain exists when objects access each other sequentially owned same user. example, stored procedure can call other stored procedures, or stored procedure can access multiple tables. if objects in chain of execution have same owner, sql server checks execute permission caller, not caller's permissions on other objects. therefore need grant execute permissions on stored procedures; can revoke or deny permissions on underlying tables.