Hello, SQL 2008 R2
We are changing our level of PCI certification and I have a question about SQL access security.

Is there a way to give someone (windows active directory logon) access to execute a stored procedure without giving them access to read and write to the database?  I was thinking of the stored procedure doing a run as, thus the user cannot directly edit the database.

Thanks

 

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

I had to do something similar to this for an audit finding.

We took away all but the most rudimentary access to the database.
The users have access to execute stored procedures, but not to read/write to the db.

The stored procs are treated as "sanctioned" code, and direct table access is prevented. You have to be careful if you have cross db or cross server queries, because you have to do things with the guest account.

In an app that took it one step further, the code actually assumed an APP ROLE, which had access to execute procedures. The users had basic login privileges, and little else.

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

Thanks for the reply.  I have problems with SQL Server security.  
It never works for me like the MS instructions say it will.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

One option is to create a new database role:
CREATE ROLE db_runprocs AUTHORIZATION DBO

Then grant your users access to just that role.

Next, grant that role to all stored procedures:
GRANT EXECUTE ON <stored proc name> TO db_runprocs

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

Yes. The default way that SQL Server security works is that users can execute stored procedures without needing access to the underlying tables.

The exception from this is if you are using dynamic SQL within the stored procedure. If you are using dynamic SQL within the stored procedure you'll need to either give the user access to do what ever the stored procedure is trying to do, or you'll need to use EXECUTE AS within the stored procedure and that user which the code within the procedure is executed as will need to have access to the base tables.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

I have had trouble in the past where a user could not execute a stored procedure even with read/write access. Solved by creating an execute role for them.

As stated I have a lot of trouble working with security. Is there a simple to understand resource I can obtain to help an aparently slow learner? As I said when I follow the Microsoft instructions it does not work as the instructions indecate, thus I am either doing something wrong (quite likely) or the instructions assume too much (MS is good at that).

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

Working on this again (finally). I have a question about the GRANT.
SQLBill said to use
GRANT EXECUTE ON <stored proc name> TO db_runprocs
My question is what is the difference between that and
GRANT EXECUTE TO db_runprocs

Thanks

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

GRANT EXECUTE ON <stored proc name> TO db_runprocs - Gives access only to that specific procedure

So if I want to give permission to all stored procedures I use GRANT EXECUTE TO db_runprocs?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

Yes, that's right.