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

2005 transactional replication statement delivery

Hi There

WHen creating publciations under 2005 i saw a very interesting option under stament delivery, for inserts , updates , deletes there is an option that simply says insert/update/delete statement.

I could find very little in BOL about this under "Article Properties", is this what it soudns like ? FOr example if you say :

update Table set Cloumn = 'whatever', this will not trigger the update sp for each row at the subscriber , will it actually deliver the update statements and literally do the update/insert/delete statement at the subscriber?

Thanx

Yes. It is equivalent to supply value 'SQL' for parameter @.ins_cmd/@.upd_cmd/@.del_cmd in sp_addarticle. For more information, please take a look at the document for sp_addarticle http://msdn2.microsoft.com/en-us/library/ms173857.aspx.

Peng

|||just an FYI, using SQL instead of the custom stored procedures has a negative perf impact, it's primarily used for non-SQL server subscribers. Stick with the custom stored procs if you can.|||

HI Greg

That is a very interresting point you bring up.

I was thinking of it exactly for performance, often in replication we perfom an update statement on the publisher that may take 10 minutes to execute and update say 2 millions rows. However this causes replication latency of up to half an hour at the subscriber at the execution of the MSupd_repl proc 2 million times is slow sometimes each execution of the proc can take a tenth of a second which is alot when you have millions of rows to update, performing the same update at the subscriber would take 10 minutes.

So i actually thought this option could help but apparantly not ?

|||

Hi Dietz,

Change delivery method to "SQL" won't help the performance as replication will still deliver the changes row by row. It means that, in your example, we still call update 2 million times. To solve this problem, you can utilize the SQL 2005 functionality "Publishing Stored procedure Execution in Transactional Replication". More info can be found here (http://msdn2.microsoft.com/en-us/library/ms152754.aspx). It will require you to use stored procedures for your update though.

Peng

|||

The performance you're seeing has nothing to do with SQL vs custom stored procs. In fact, if you used SQL, I bet it owuld be much slower since with SQL, multiple commands are batched then sent via one parameterized statement. With custom stored procs, the proc is compiled once, with SQL, you're pretty much guaranteed that no two batches will be the same, thus you'll have a ton of compilations.

What you're experiencing can be worked around by replicating the execuction of a stored procedure. http://msdn2.microsoft.com/en-us/library/ms152754.aspx.

|||

Thanx Pend and Greg for the feedback, but how is this different from the usual method if it will still call update 2 millios times. DO you mean that instead of calling the MSupd sp on the sunscriber it delivers and update statement for each row?

I am aware of the sp publication method which was available in 2000 i thought this was somehow a similar concept. Thanx

|||If you issue a single update statement at the publisher that affects 2 million rows, you will get 2 million update statements, one for each row, replicated to the subscriber regardless whether you use SQL or custom stored procedures. If you do these batches often, not only can you experience long latency but your distribution database will fill up quickly. In these cases, it's recommended to replicate the execution of a stored procedure if latency and disk space is an issue. By putting your update statement in a proc, and replicating proc execution, rather than send 2 million deletes, it sends the proc call to the subscriber. Now you only have one command instead of 2 million.|||

Hi Greg

Yes that is wagt i thought, however we are getting rid of replication, but good to know.

Thanx