Sql Server - Basic Permissions Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ranginald - 27 Aug 2006 23:40 GMT

I have a SQL Server database and I want a user to be able to execute
stored procedures only. These stored procedures use SELECT and WHERE
statements and return integers.

I have set EXECUTE permissions to the sp's as needed for the user.

It is possible to allow a user to have EXECUTE permissions on the sp's,
but deny everything else? E.g. If id did denyreader and allowed
EXECUTE on the sp's, would the command fail because the sp's have
SELECT and WHERE statements?

Thanks!
-Ranginald

Reply to this Message

David Browne - 28 Aug 2006 03:37 GMT

>I have a SQL Server database and I want a user to be able to execute
> stored procedures only. These stored procedures use SELECT and WHERE
[quoted text clipped - 6 lines]
> EXECUTE on the sp's, would the command fail because the sp's have
> SELECT and WHERE statements?

Basically this will work.

Look up "ownership chains" in BOL. Whan a user is granted EXECUTE on a
stored procedure, SELECT (and other) permissions checks are supressed
durring the execution of the stored procedure so long as the owner of the
target table is the same as the owner of the stored procedure.

David

Reply to this Message

Uri Dimant - 28 Aug 2006 06:14 GMT

> It is possible to allow a user to have EXECUTE permissions on the sp's,
> but deny everything else? E.g. If id did denyreader and allowed
> EXECUTE on the sp's, would the command fail because the sp's have
> SELECT and WHERE statements?

Yes , it will work. an User will be able to execute a SELECT statement even
he/she is a memeber of db_denydatareader database role

>I have a SQL Server database and I want a user to be able to execute
> stored procedures only. These stored procedures use SELECT and WHERE
[quoted text clipped - 9 lines]
> Thanks!
> -Ranginald

Reply to this Message

Arnie Rowland - 28 Aug 2006 15:31 GMT

You are making very good choices. A good security model includes adding all
users to both db_denyReader and db_denyWriter, and providing ALL data access
through Stored Procedures.

On cravat is to be sure that all tables and stored procedures are 'owned' by
the dbo schema. Make a practice of always adding 'dbo.' to the object name
in the create scripts. (e.g., CREATE PROCEDURE dbo.MyStoredProcedure)

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

>I have a SQL Server database and I want a user to be able to execute
> stored procedures only. These stored procedures use SELECT and WHERE
[quoted text clipped - 9 lines]
> Thanks!
> -Ranginald

Reply to this Message