Re: prevent users from modifying tables or data but allow creating and modifying store procedures

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 18:33:19 -0400

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!!
>
>

Relevant Pages

  • Re: prevent users from modifying tables or data but allow creating and modifying store procedures
    ... but they wouldn't be able to execute it (even if only ... because they could easily put ALTER TABLE or UPDATE in there? ... With SQL Server 2005, you could roll back unauthorized table mods ... > but allow creating and modifying store procedures? ...
    (microsoft.public.sqlserver.security)
  • Re: Problem altering table and adding a default
    ... ALTER TABLE dbo.Test_tbl ADD Notnull_col1 charNOT NULL ... SQL Server does not allow that - you can only add nullable columns. ... After you add a new column in Enterprise Manager, uncheck Allow Nulls, bind the uder- ... Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
  • Re: Problem altering table and adding a default
    ... SQL Server does not allow that - you can only add nullable ... > After you add a new column in Enterprise Manager, uncheck Allow Nulls, ... the ALTER TABLE statement above will pass to the SQL Server ... > 4) Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Express - Identity specification property - how to change
    ... UPDATE tbl ... You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: user defined Role - HELP
    ... I get an error "Privilege ALTER TABLE may not be granted or revoked. ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Grant the role ALTER permission on the table. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)