Re: Execute stored procedure only


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.


"Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message

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


"Erland Sommarskog" wrote:

Andy (Andy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I want to create a user call "limited" in sql 2005. This user should
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
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
Books Online for SQL Server 2000 at


Relevant Pages

  • Re: DB access for web apps
    ... For stored procedures in your database, you will have to give an explicit ... EXECUTE permission to this user. ...
  • Re: Execute stored procedure only
    ... right to execute stored procedure in database called "mydatabase". ... deny on every permission and checked grant on execute. ...
  • Re: EXECUTE permission denied on object... uh?
    ... connecting with doesn't have permission to execute the Stored Procedure ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
  • 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 ...
  • 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 ...