user to execute nothing but SP in 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AM - 19 Jul 2007 18:22 GMT

Hi All,

Can someone please help me with creating a user just to execute store
procedure and nothing else.
What explicit permission I need to give. I am trying to deny all permission
and then just grant Execute (I also tried to grant connect and select) and
still does not work.
Am I missing something obvious? Again I don't want this user to do or view
anything other then execute certain stored procedure and just be able to see
any data related or executed through stored procedure.

Thanks in advance.
AM

Reply to this Message

Erland Sommarskog - 19 Jul 2007 23:14 GMT

> Can someone please help me with creating a user just to execute store
> procedure and nothing else.
[quoted text clipped - 4 lines]
> view anything other then execute certain stored procedure and just be
> able to see any data related or executed through stored procedure.

If the user is only supposed to be able to run one single stored procedure,
then it should be sufficient with granting him EXECUTE on that procedure.
As you as you have not granted any permissions to public, that is. (Or
added the user to a role with permissions.) Denying him SELECT, INSERT,
UPDATE and DELETE on the dbo schema or the database, is still a good
idea. You may also want to DENY him VIEW ANY DATABASE on server level.

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

AM - 20 Jul 2007 01:00 GMT

Thanks Erland.

> > Can someone please help me with creating a user just to execute store
> > procedure and nothing else.
[quoted text clipped - 11 lines]
> UPDATE and DELETE on the dbo schema or the database, is still a good
> idea. You may also want to DENY him VIEW ANY DATABASE on server level.

Reply to this Message