0

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?

6
  • Are you testing the functionality by logging in as foo, or are you using EXECUTE AS USER = N'foo'; to test? The latter is unreliable for cross-database testing as the functionality does not support many cross-database boundaries, including permissions context. If you're getting the same error when logging in as them (which would be weird, since the error references sa), you could sign with a certificate (see Erland's post; you could also see if a local view or TVF could be used to access the data in the other database. Apr 22, 2015 at 18:45
  • Aaron: I am connecting as foo in SSMS, not using EXECUTE AS. If I "GRANT SELECT ON SCHEMA :: dbo TO [foo];" in database B, it works. However, foo can then directly SELECT from the tables and views in database B, which is unacceptable. The procedure I am testing with just does a SELECT from a local view, which in turn accesses tables from databases A and B. I'll read Erland's post and see if I can figure out a solution there. Thanks! Apr 22, 2015 at 19:12
  • Is GRANT SELECT ON SCHEMA in the other database not acceptable because you don't want to give the user access to all tables and views in that schema? Did you try just GRANT SELECT ON <the tables they need to see>? Apr 22, 2015 at 19:18
  • I don't want them to be able to access any tables except by going through the procedures. Apr 22, 2015 at 20:06
  • Got it. Yeah, hopefully Erland has a workable solution for you. Apr 22, 2015 at 20:06

1 Answer 1

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.