Re: user to execute nothing but SP in 2005

AM (AM@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
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.

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.



--
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: SQL CLR Sproc isnt running right
    ... did you grant rights using the SQL Server 2005 Surface Area Config tool? ... and I got a permissions error back saying DBO doesn't have permission to insert records into the table... ... but the assemblie's stored procedures do all have execute permissions set for the role that the executing user is in... ... "William Vaughn" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Executin DTSrun Utility under the xp_cmdshell
    ... When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NTR command shell that the account running Microsoft SQL ServerT has the needed privileges to execute. ... You may, however, grant other users permission to execute this stored procedure. ... When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. ...
    (microsoft.public.sqlserver.dts)
  • Re: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.security)
  • Re: Execute stored procedure only
    ... Should I not be able to just execute SP ... What I meant is that since you denied every database permission you saw, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2005 execute permissions
    ... If the user has a permission to create an object (he is an owner). ... to allow a user to create a new procedure and then grant execute ... Grant execute permissions for that role for that procedure.. ...
    (microsoft.public.sqlserver.security)