Sql-server – How to limit a user to only be able to run procs in a schema, which access another db

permissionsschemasql-server-2012stored-procedures

In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything else. For example, they should not be able to SELECT directly from any view or table in either database.

The stored procedures SELECT from tables in databases A and B. I created the procedures WITH EXEC AS OWNER. Database A is owned by sa, which has the sysadmin role and should therefore have access to both databases. I ran this:

USE [master]
GO
CREATE LOGIN [foo] WITH PASSWORD=N'foo', DEFAULT_DATABASE=[A]
GO
USE [A]
GO
CREATE USER [foo] FOR LOGIN [foo]
ALTER ROLE [db_executor] ADD MEMBER [foo];
GO
GRANT EXECUTE ON SCHEMA :: [Bar] TO [foo];

I then connected as [foo] and executed the procedure, but received the error:

Msg 916, Level 14, State 1, Procedure GetReport, Line 10 The server
principal "sa" is not able to access the database "A" under the
current security context.

Setting TRUSTWORTHY ON seems to open things up much more than is acceptable. Is there another solution?

Best Answer

I am reluctant to post this as an answer, because I don't have a tangible example to post with code, but I pointed Mark to Erland Sommarskog's great article, Giving Permissions through Stored Procedures, which has a section on Signing Procedures with Certificates.

Cert signing seems to be the right approach for forcing data access through a procedure without opening up all kinds of other security holes, which can happen with ownership chaining, using TRUSTWORTHY, elevating the user to a higher role, or granting them direct access to the tables (and thereby giving them a route to bypassing your procedures). I will mock up an example here as time allows (won't be today, unfortunately).

Related Question