How to control access to a Stored Procedure in SQL Server?

Question

I want to be able to have a Stored Procedure that can only be used from a particular page, without having to create a permissions / role for a user, when it is just a single stored procedure I want accessed in this way.

For example I want to have a proc_GetCustomerItems stored procedure which takes the Parameter of CustomerID and then returns a list of Items a Customer has purchased, however I want this to only work for the place which lists the Customer Items eg. the ASP Page which shows this list, but not work from anywhere else, such as Query Analyser etc.
Would this be done with another parameter to provide additional security information, the basic issue is how to allow only certain Methods and Pages access to a Stored Procedure, could this be done with a Role or Permissions?

The main issue is how to set the access rights to a Stored Procedure correctly to a particular method in code, which will help enforce the use of said Stored Procedure, so one has to explicitly allow a GetCustomerItems method (in ASP for example) access to use this Stored Procedure.

I'm guessing Roles and User Access will be the suggested alternative, but I would be interested if it is possible to restrict access to a Stored Procedure by where it is called from.

Answer 1

No, this is not possible. The nearest I can imagine you getting to this is the page that only has access to the certain stored proc uses different credentials from the rest of the pages. If you want people to not access the proc using Query Analyser you can use the standard SQL Server security features.

Answer 2

The closest you could get would be to add code to the beginning of your stored procedure that queried the values of APP_NAME(), HOST_NAME(), HOST_ID(), CURRENT_USER and perhaps others, and if any didn't match the relevant values for your app then just return NULL or an empty row. This wouldn't be page-specific, of course, and there wouldn't be anything to stop someone using Query Analyzer to view the contents of the stored procedure itself.

Answer 3

Hard to know whether it would be suitable for you or not, and usually I'm pro-stored procedures but the only alternative I could suggest is that you don't use a stored procedure.

Instead, use parameterised sql from the page in question. This way, there's no stored procedure existing to be used from anywhere else.

This would mean, if not already the case, that the user account being used would need direct permissions on the underlying tables.