SQL Stored procedure access?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Erik Reiter - 24 Jan 2008 17:01 GMT

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?

Thanks,
Erik

Reply to this Message

Erland Sommarskog - 24 Jan 2008 22:47 GMT

> 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
[quoted text clipped - 4 lines]
> Enterprise Manager, but not execute the Insert, Update, and Delete
> Stored Procedures?

I will have to start to ask which version of SQL Server you are using.
When you talk about schemas, that makes me think that you are on SQL 2005,
but then you mention Enterprise Manager which is for SQL 2000/7 only,
so I don't really know.

And when it comes to security, the answers for SQL 2000 and SQL 2005 are
often quite different, as SQL 2005 saw a great overhaul in this area.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message

Erik Reiter - 25 Jan 2008 00:17 GMT

Sorry about the confusion I am using SQL Server 2005 and Microsoft SQL Server
Management Studio.

Thanks,
Erik

Reply to this Message

Erland Sommarskog - 25 Jan 2008 22:57 GMT

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.

Signature

Reply to this Message