SEO

#1 (**permalink**) 02-29-2008, 07:50 PM

Please help.
I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?
Thank you for information
/RAM/

#2 (**permalink**) 02-29-2008, 07:50 PM

No permissions exist when a new object is created. Only privileged users
such as the object owner, sysadmin role members, the database owner and
db_owner role members have access until permissions are granted. To grant
only UPDATE permissions:

GRANT UPDATE ON dbo.MyTable TO SomeRole

--
Hope this helps.

Dan Guzman
SQL Server MVP

"R.A.M." <r_ahimsa_m@poczta.onet.pl> wrote in message
news:4ltpa2hsjphrsifi2voj6kdr0vfcuh2f0c@4ax.com...
> Please help.
> I have a table with single row. I need to allow only UPDATEs of the
> table, forbid INSERTs and DELETEs. How to achieve it?
> Thank you for information
> /RAM/

#3 (**permalink**) 02-29-2008, 07:51 PM

R.A.M. (r_ahimsa_m@poczta.onet.pl) writes:
> I have a table with single row. I need to allow only UPDATEs of the
> table, forbid INSERTs and DELETEs. How to achieve it?

CREATE TRIGGER update_only FOR INSERT, DELETE AS
RAISERROR('INSERT and DELETE not permitted on this table!', 16, -1)
ROLLBACK TRANSACTION

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4 (**permalink**) 02-29-2008, 07:51 PM

On Thu, 06 Jul 2006 13:42:39 +0200, R.A.M. wrote:

>Please help.
>I have a table with single row. I need to allow only UPDATEs of the
>table, forbid INSERTs and DELETEs. How to achieve it?
>Thank you for information
>/RAM/

Hi RAM,

Dan already replied how to do this with GRANT and DENY. If you also must
keep the database owner and administrators from accidentally inserting
or deleting a row, add the following trigger:

CREATE TRIGGER NoInsertOrDelete
ON SingleRowTable
AFTER INSERT, DELETE
AS
IF @@ROWCOUNT = 0 RETURN
ROLLBACK TRANSACTION
RAISERROR ('Don''t add rows to or remove rows from this table!', 16, 1)
GO

--
Hugo Kornelis, SQL Server MVP

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

2005: forbidding INSERTs and DELETEs