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?

  • 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. – Aaron Bertrand Apr 22 '15 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! – Mark Freeman Apr 22 '15 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>? – Aaron Bertrand Apr 22 '15 at 19:18
  • I don't want them to be able to access any tables except by going through the procedures. – Mark Freeman Apr 22 '15 at 20:06
  • Got it. Yeah, hopefully Erland has a workable solution for you. – Aaron Bertrand Apr 22 '15 at 20:06
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, privacy policy and cookie policy

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