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



.