Sqlserver stored procedure to execute DML on behalf of another user

Question

Requirement:

User A can log in to SS instance, but only has access to database X. User A has no other access to any objects.

database X has a stored_proc called "sp_exec_dml" which takes a DML string and executes it. This stored proc would(should?) run as the owner X.

does database X, or the stored procedure ""sp_exec_dml" then need access to other dbs/objects.

for example

user A executes

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

I hope this makes sense. I come from an Oracle background so exactly how permissions are granted and who the grantees can be is confusing.

do databases or stored procedures have access granted to them

thanks

Answer 1

If database X has "table" in

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

then the user should have access.

Answer 2

According to the MSDN : (URL : https://msdn.microsoft.com/en-US/library/ms345484.aspx )

To grant permissions on a stored procedure

In Object Explorer, connect to an instance of Database Engine and then expand that instance.

Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.

From Stored Procedure Properties, select the Permissions page.

To grant permissions to a user, database role, or application role, click Search.

In Select Users or Roles, click Object Types to add or clear the users and roles you want.

Click Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.

In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions,

see Permissions (Database Engine).

Selecting Grant indicates the grantee will be given the specified permission. Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals.

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

Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.