prevent users from modifying tables or data but allow creating and modifying store procedu

Hello
Is there a way in Sql Server to prevent a user from modifying tables or data
but allow creating and modifying store procedures?
thanks!!
Hmmm, that would be a strange one. I guess you would want them to be able
to create a procedure, but they wouldn't be able to execute it (even if only
to test it), because they could easily put ALTER TABLE or UPDATE in there?
You can prevent writing of data by:
USE <database>
GO
EXEC sp_addrolemember 'db_denydatawriter', '<user name>'
GO
As for allowing CREATE PROCEDURE but not ALTER TABLE, hmm, not sure about
that one. With SQL Server 2005, you could roll back unauthorized table mods
using DDL triggers...
http://www.aspfaq.com/
(Reverse address to reply.)
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>
|||right click database, properties, permissions tab.
there are check boxes you can use to deny users to create table but allow
them to create sp.
steve
"Zeng" <zzy@.nonospam.com> wrote in message
news:uKtrn3tjEHA.1644@.tk2msftngp13.phx.gbl...
> Hello
> Is there a way in Sql Server to prevent a user from modifying tables or
data
> but allow creating and modifying store procedures?
> thanks!!
>