Execute without Insert

Is there a way to let an account have execute permission on a stored
procedure but not let that stored procedure run insert , delete, or
update records. Basically only let them run or create stored
procedures that do selects.[posted and mailed, please reply in news]

HD (harlan@.elementalcomponents.com) writes:
> Is there a way to let an account have execute permission on a stored
> procedure but not let that stored procedure run insert , delete, or
> update records. Basically only let them run or create stored
> procedures that do selects.

I'm not really sure what you are asking. You could have a user which have
permissions to create procedure, but only has SELECT permissions on the
tables. In such case, the procedures of that user only perform SELECTs,
no updates.

But if you are asking if you somehow can say that a user may only execute
stored procedures that performs read-only operations, there is no way
to do this by a single setting, at least not what I can think of.

But you can of course grant execute permissions selectively. And to find
out which procedures that performs updates, you can use this select:

select name
from sysobjects o
where o.type = 'P'
and exists (select *
from sysdepends d
where d.id = o.id
and d.resultobj = 1)

However, a word of caution is that this query may not return all updating
procedures. For instance, if you create a procedure first and then the
tables it refers to, there will not be any dependencies recorded.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp