Re: SQL Stored procedure access?

Erik Reiter (ErikReiter@xxxxxxxxxxxxxxxxxxxxxxx) writes:
I have a database with multiple schemas and each contains Tables, Views
and Stored Procedures. My users are on the intranet. I have a windows
application that the users run to Select, Insert, Update, and Delete
data from the database through the Stored Procedures and Views. No
users have access to the base tables in the database. My question is:
How do I allow the users to run queries against the views in SQL Server
Enterprise Manager, but not execute the Insert, Update, and Delete
Stored Procedures?

Now I know which version of SQL Server you are using, I can ask about
the test.

If I understand this correctly, you have a bunch of stored procedures
that you want users to be able to run from the application. However, you
do not want them to run the procedures directly from a query tool. Is
that correct?

To achieve this, you may need to re-architecture your application a bit.
Is this a two-tier application or a three-tier application?

If it is a two-tier application, there is no way to solve this fully
securely. You can use an application role that you grant permission on the
stored procedures. The application activates the application with
sp_setapprole. However, the application must get the password from
somewhere, and it must be from somewhere the user has read access, so if
the application can find it, the user can too. But if you only want
protection against casual usage, an application role may be good enough.
You should also beware that using application roles with connection
pooling requires some extra care.

In a three-tier application, you can hide the password to the approle
somewhere the users do not have read access. A three-tier application
also permits you to use a proxy user that logs on behalf the users. The
proxy user can then impersonate the real users with EXECUTE AS. This is
a nice alternative, beacuse the users can be created without login, and
the users themselves do not have any direct access to SQL Server. Since
you want your users to be able to access the views through SSMS, this
is not applicable to you. Then again, you could have two set of database
users, one for application access and one for SSMS access.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Unable to view system stored procedures from .NET IDE and unable to debug SQL
    ... But what is the reason for the debug problem I mentioned below? ... try to step into a stored procedure in the Northwind database, ... Run SQL server setup or contact database ... I can't see any system stored procedures in master. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Identical database w/ identical stored procedures?
    ... > creating the maintence nightmare of updating the same stored procedure ... First of all, put your source code, tables, stored procedures and all ... This can be achieved with a help table in the database. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Does nesting stored procedures make sense to increase performance?
    ... Reducing client or middle-tier round trips to the database is a good idea in that it will reduce network traffic. ... In SQL Server 2000, because recompiles of query plans are done on a per-procedure basis, it's helpful some execute one SQL statement per procedure as well. ... If you're going to combine many calls into a single call, you'll need to add robust error handling to the "driver" procedure so as not to call procedures 2,3, and 4 if procedure 1 fails, etc. You'd need to replicate your existing client/middle-tier code's concept of success/failure results quit the batch of procedures on a failure and the return the results/return code that the client/middle-tier sees in the original. ... I've recently became aware of nesting stored procedures in SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Debugging in VS.NET
    ... > "Cannot debug stored procedures because the SQL Server database is not ...
    (microsoft.public.sqlserver.msde)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)