October 30, 2002 at 2:14 pm
Does anyone know how I could deny sysadmin users from modifying data in a table?
As far as I am aware there is no way, the best I can do is audit who is making the changes using triggers.
The problem that I am faced with is storing some sensitive information that I only want to insert and update using specific tested stored procedures.
Does anyone have any ideas?
October 30, 2002 at 2:40 pm
Unfortunately, there is no way to do that. Sysadmins, by definition, can always do everything on a SQL Server. Your best bet is to limit access to sysadmin accounts to a small set of individuals, so that you can control the behavior of sysadmins by educating them on proper behavior.
Matthew Burr
October 31, 2002 at 2:16 am
And you can use windows authentification for the sysadmins so that you know who is doing something.
October 31, 2002 at 4:05 am
Not really a fail safe but you can fix the trigger to check for the person making changes and do a RAISERROR. I do this to prevent deletes on a control table in one of my databases.
CREATE TRIGGER tr_nodelsontblx
FOR DELETES
AS
RASIERROR('Sorry deletes are not allowed on this table', 11, -1)
RETURN
This works great unless they perform something like a TRUNCATE TABLE which is none logged and not triggerable. I can also check to see if they are DBO in equivalent and let only DBOs do the work. I believe you can check roles as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply