Q:

Read Only Access Except Stored Procedures in Specific Schema

I'm building an application that is attached to a legacy application database. The application has it's own database that is on the same server as the legacy database. 
My application is ASP.NET and I'm using Entity Framework.  I've set up a domain user that is a DB owner for my application database and has read-only access to the legacy database.  I don't want to accidentally make any updates to the legacy database through some bug in the application.
I would like to create stored procedures in my new database that can modify data in the legacy database.  I figured putting them in their own schema might be a logical start, at least for the sake of organization.  I understand schemas are good for organizing security access.  Is there a good approach I can take to make this work?

A:

If you are looking to create embedded sql-inline Linq queries within Entity Framework, you can assign dbreader or dbwriter permissions appropriately to the application login.
Unfortunately currently in SQL Server, you cannot create specific access to Stored Procedures which only conduct read operations as a category. You will have to grant individual reader or write access to the specific stored procedures by 
Grant execute on Stored procedure to user, etc.

