Sql-server – Permissions to execute a procedure on database a that reads a table on database b

permissionssql serversql-server-2008-r2

A user in database a has execute permission on a stored procedurte in database a, that stored procedure reads table 1 in database b however when the user executes that procedure they get the following message.

The SELECT permission was denied on the object '1', database 'b', schema 'dbo'.

If I give the user db_datareader permission in database b then the procedure works.

I didn't think I needed to do that though. Please can somebody explain how wrong I am!

Best Answer

This is an issue of ownership chaining and how that doesn't automatically translate across databases as most people would expect. The following MSDN page has a good explanation of what is going on in this situation:

Ownership Chains

That article states that in order to get this to work as most would expect, you need to enable "Cross-Database Ownership Chaining". However, this is a security risk, and that article even has a section for "Potential Threats".

The more secure way to extend permissions is by using module signing. Please see my answer on the following Question (here on DBA.SE) for how to remedy this, especially if you don't want to give the user any access to Database B:

stored procedure can select and update tables in other databases - minimal permissions granted

The first time you go through this exercise it might seem terribly complicated, but after a few times it will make enough sense to not seem to bad ;-).

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

Sql-server – Select permission inside stored procedure

Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure.

What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a moot point, and you will need to grant explicit permission on the objects that your dynamic SQL is using.

If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.

The below link from Microsoft should help you with your problem:

PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure (Wayback Machine archive)

This behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.

This is also discussed in the (more current) Microsoft Docs article:

Writing Secure Dynamic SQL in SQL Server

Executing dynamically created SQL statements in your procedural code breaks the ownership chain, causing SQL Server to check the permissions of the caller against the objects being accessed by the dynamic SQL.

Best Answer

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Sql-server – Select permission inside stored procedure

Related Question