By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,008 Members | 1,235 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,008 IT Pros & Developers. It's quick & easy.

2005: forbidding INSERTs and DELETEs

P: n/a
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/
Jul 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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_********@poczta.onet.plwrote in message
news:4l********************************@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/

Jul 6 '06 #2

P: n/a
R.A.M. (r_********@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, es****@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
Jul 6 '06 #3

P: n/a
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
Jul 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.