July 6, 2010 at 11:47 am
Hi
I know its possible to prevent the execution of deletion command for certain tables by adding a instead of trigger on those tables.
But, how do you go about triggering / (avoiding /recording) the deletion of ANY table in a database? Probably using systables?
(Without considering - Not granting permission for delete to users)
July 6, 2010 at 12:05 pm
You need a trigger on every table. You can write up some code based on sys.tables to create the triggers, but there will have to be one for each table that you want to check/prevent deletion on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2010 at 4:33 pm
Are you talking about row deletion or table deletion (DROP)?
Scott Pletcher, SQL Server MVP 2008-2010
July 6, 2010 at 10:37 pm
Row deletion.
July 7, 2010 at 7:59 am
Yes, you need a trigger on every table.
You could try DENYing them DELETE (which is stronger than just not GRANTing them permission) as a good first step. But that's not a guarantee of preventing them DELETE altogether, so you would likely still want the triggers.
Scott Pletcher, SQL Server MVP 2008-2010
July 7, 2010 at 9:08 am
What do you mean by DENY?
July 7, 2010 at 9:33 am
There is a separate DENY command:
DENY DELETE ON <tablename> TO <user/role>
I think you can even specify multiple tablenames and/or users and roles in the same command.
[EDIT: You cannot specify multiple tablenames in one command, only multiple users/roles.]
DENY overrides GRANTs and will still prevent DELETE from that table, unless a user override/context switch has occured (such as "EXEC AS").
DENY is strong, but you probably still can't rely on it 100% (?).
Scott Pletcher, SQL Server MVP 2008-2010
July 7, 2010 at 9:36 am
http://msdn.microsoft.com/en-us/library/ms188338%28SQL.90%29.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2010 at 9:41 am
scott.pletcher (7/7/2010)
DENY overrides GRANTs and will still prevent DELETE from that table, unless a user override/context switch has occured (such as "EXEC AS").
Or the user is sysadmin.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2010 at 9:45 am
I have tested DENY, even up to db_owner, and it works (interesting that it worked even for db_owner).
But my extensive testing was under SQL 2000. Naturally you'll want to do your own testing for various security levels.
DENY will certainly be pretty effective even without other steps. I would use it even with triggers, since it's way less overhead to catch it in the security check phase than in a trigger.
Scott Pletcher, SQL Server MVP 2008-2010
July 7, 2010 at 9:52 am
scott.pletcher (7/7/2010)
I have tested DENY, even up to db_owner, and it works (interesting that it worked even for db_owner).
Yes, but sysadmins (and by that I mean only members of the syadmin fixed server role) bypass all security checks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2010 at 9:55 am
DENY doesn't work with sysadmins, as Gail pointed out.
DENY won't prevent db_owners from dropping the DENY itself (db_owner implies CONTROL).
I experienced some strange things with DENY, so I'd be careful.
It once happened to me that DENY ALTER worked for a windows login and didn't work for a SQL login with identical permissions. It was a SQL Server 2005 SP2. I was never able to reproduce it, but I'm quite sure it did happen.
-- Gianluca Sartori
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply