Re: Execute stored procedure only

Andy,

You need CONNECT capability to be able to get into the database and execute
the stored procedure.

You can compare this to having permission to eat a piece of pie, but it is
behind a locked door where you cannot get to it. So, the permission does you
no good until you can get through the door.

RLF


"Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A5FCB514-16C0-4A4C-B9ED-ED86E1EC99A5@xxxxxxxxxxxxxxxx
Erland,

You are right, I did try to connect through Query Analyzer granting
connect
right, jsust to test. Should I not be able to just execute SP through QA?

Thanks
A

"Erland Sommarskog" wrote:

Andy (Andy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I want to create a user call "limited" in sql 2005. This user should
not
be able to do anything (Select, alter, create update.....etc ) Only
right to execute stored procedure in database called "mydatabase". In
the permission column in database I am selecting the user and checked
deny on every permission and checked grant on execute.
When I try to execute stored procedure I am getting folowing error
" Msg 916, Level 14, State 1, Line 1
The server principal "limited" is not able to access the database
"mydatabase" under the current security context.
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'dbo.spabc123'.
Am I way out in my thinking that this is possible and has to be done
this
was, or is there a better way?
I want to make sure all other rights are specfically denied.

I don't think so. It appears that you want to grant the user CONNECT
permission. Else you would not be complaining about that error.

--
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: Execute stored procedure only
    ... right to execute stored procedure in database called "mydatabase". ... deny on every permission and checked grant on execute. ...
    (microsoft.public.sqlserver.security)
  • Re: DB access for web apps
    ... For stored procedures in your database, you will have to give an explicit ... EXECUTE permission to this user. ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE permission denied on object... uh?
    ... connecting with doesn't have permission to execute the Stored Procedure ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: Security context of stored procedure
    ... Suppose there are three users in a database TEST_CHAIN, ... simple stored procedure: ... also grant it to test_sp2 ... SELECT permission denied on object 'authors', database 'test_chain', owner ...
    (microsoft.public.sqlserver.security)
  • Re: Quick code to execute an SQL stored procedure.
    ... There are alot of post where people don't google for an answer, ... But you are right I googled for "MFC Stored Procedure In 3 Lines of Code" ... I figured that a stored procedure is like a function call to a database. ... I'd use the handle and then call some kind of execute ...
    (microsoft.public.vc.mfc)