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?