CODEWITHSUNDEEP
sqlsql-serverdatabasepersistencereadonly
Giffyguy
Giffyguy

Reputation: 21302

Is it Possible to Enforce Read-Only Fields in SQL Server 2008 R2?

I'd want to prevent UPDATE querys that write over ceartain fields in my database. I also want to prevent UPDATE or DELETE querys of any kind on certain tables, as these tables contain permanent information that needs to persist indefinitely.

Can these configurations be implemented in SQL Server Management Studio?

Upvotes: 0

Views: 1937

Answers (3)

gbn
gbn

Reputation: 432662

  1. Remove all permissions for read only tables. No-one can make changes then

  2. Consider stored procs or views to control writes, again remove direct permissions

  3. See marc_s answer

If your users connect as dbo or sa then there is nothing you can do that is effective. Users can disable trigegrs or delete them. With sa permissions are never checked anyway.

Upvotes: 4

Lamak
Lamak

Reputation: 70678

@marc_s is right, if you want to prevent changes in some fields you should use a trigger or limit the user's permissions if you are the dba. In any case, instead of an AFTER trigger i would use an INSTEAD OF trigger, so it wouldn't be necessary to rollback the transaction.

CREATE TRIGGER triggerName 
ON dbo.YourTable INSTEAD OF UPDATE, INSERT 
AS    
IF UPDATE(somefield)       
    -- do nothing or whatever

In any case, if you find this answer correct, please accept the answer provided by @marc_s, this is just something extra over that answer

Upvotes: 1

marc_s
marc_s

Reputation: 755451

In order to prevent updates on certain fields, you'll probably have to have an AFTER UPDATE trigger on that table that would check if any of the "read-only" fields is about to be updated; if so, abort the transaction.

CREATE TRIGGER triggerName
ON dbo.YourTable AFTER UPDATE
AS
   IF UPDATE(somefield)
      ROLLBACK TRANSACTION

To prevent certain users access to certain tables, just don't grant those users (or a database role they belong to) the UPDATE and/or DELETE permission on those tables.

Upvotes: 8

Related Questions