Execute rights on Stored Procs

Execute rights on Stored Procs

Post by Ken Nickel » Fri, 18 May 2001 04:24:26


I am trying to establish a security model where the only rights a group or
role has is execute rights on stored procedures. All other rights are
revoked for this group and the user who belongs to the group has no rights
at all. I believe BOL says this is a viable security model. The proc I
created for testing is very simple

update table
set column = value
where id = 123

The problem is that sql2000 (compatabilty level=8) complains saying that the
user does not have "select" or "update" rights, but on sql 65 it works as
advertised. In sql2000 the user is a member of a role and that role has only
execute rights on that stored proc. Any ideas?

Thanks very much
-Ken Nickels


Execute rights on Stored Procs

Post by Wayne Snyde » Fri, 18 May 2001 05:08:34

This should work, but only if the chain of ownership is unbroken... that
means that the owner of the stored procedure is the same as the owner of the

Wayne Snyder MCT,MCSE, Sql Server MVP
IKON Education Services, C*te, NC
(Please respond to newsgroup unless otherwise requested.)


1. Stored procs in an Execute SQL Task

I have stored procedures that do either an update or insert.  I would like
to allow a certain number of errors, so the stored procedures return the
error count.

How can I execute a stored procedure and move on to the next step if the
error count is less than, say 10?

I have been trying to set a global variable from an Execute SQL Task, but
have not figured out how to put the return value into into the global.  If I
can set the global, I can use an ActiveX Script to determine whether or not
to proceed to the next step.  This seems cumbersome, is there a better way?

Thanks for any help.


2. Pick uniVerse Unidata SystemBuilder D3 Programmers needed for AZ, OH, MD and Nationwide U.S. opportunities-(Recruiter)

3. Grant Execute, stored procs, and variables.....

4. Multiple screen forms in 4GL

5. How to execute stored procs from A97 client

6. ayuda objetos ole incrustados!!!!!

7. Can't get stored procs to execute from VB5

8. Problem querying Paradox 9 w/ Microsoft ODBC driver

9. Executing stored procs

10. DB-Library Error, SqlSrv process dye when executing extended-stored-procs

11. Database role to execute user stored procs

12. ADO & SQLOLEDB, Executing stored procs

13. Executing Stored Procs