Re: Execute stored procedure only

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: 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: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure to drop and create table for end user
    ... > having permission on the database that they should not (like delete rows and ... > such) so the only thing the end user has access to is execute permission on ... > back with select permission not granted on a table the stored procedure is ... > tables (which i dont like doing considering some of them have sensative data ...
    (microsoft.public.sqlserver.programming)
  • Re: Security context of stored procedure
    ... It seems that cross database tables are completely blocked from stored ... > grant exec ... > simple stored procedure: ... SQL Server will not check the permission of the ...
    (microsoft.public.sqlserver.security)
  • RE: execute SP on the dataSet
    ... >So is it possible to execute stored procedure on my DataSet? ... I have for example 3 tables in my database which is on SQL server. ... Than I have Stored procedure, which returns the data from that tables ...
    (microsoft.public.dotnet.framework.aspnet)