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