2005: forbidding INSERTs and DELETEs

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/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.plwrote in message
news:4ltpa2hsjphrsifi2voj6kdr0vfcuh2f0c@.4ax.com...

Quote:

Originally Posted by

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/

|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 06 Jul 2006 13:42:39 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>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