Stop dbo from deleting rows ?

Question

Stop dbo from deleting rows ?

Peter van Eck

SSC Enthusiast

Points: 188
More actions
July 1, 2004 at 4:47 am

#88154

Hi there,
Does Anybody know if it's possible to deny delete rights on a table for a user with a db_owner role ?
We're facing the problem that from time to time rows are deleted from a crucial table..looked everywhere in code ofcourse, but connot find anything.Now we simply want to remove delete permission for all users for this table but in test our user can still delete rows form this table (because he is connected to db_owner role ??)
Any suggestions welcome !

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

modify the permissions for the public role and place a DENY (red cross in enterprise manager) on the delete section of this table.

is this user also a member of System Adminisrators? that could be the problem

MVDBA

Leon Bakkers SSC Eights! Points: 959 More actions · Answer 2

Hi,

Create a trigger on the table

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'deny_deletion' AND type = 'TR')
   DROP TRIGGER deny_deletion
GOCREATE TRIGGER deny_deletion
ON table_name
FOR DELETE
AS
   RAISERROR('You are not allowed to delete records', 16, 1)
   ROLLBACK TRANSACTION
GO

Regards,
Leon Bakkers

Peter van Eck SSC Enthusiast Points: 188 More actions · Answer 3

Deny permission is set for all users, still my user can delete on the table, after your message I unchecked the SA role for this user, but still same result.

When I create a brand new user all this is working perfectly, but not on the user we use at our customers...

Peter van Eck SSC Enthusiast Points: 188 More actions · Answer 4

I was thinking of an INSTEAD OFF trigger..is there any difference with your suggestion ? what is best ?

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

is it an NT username that is a member of domain Admins or local admins?

MVDBA

Leon Bakkers SSC Eights! Points: 959 More actions · Answer 6

INSTEAD OFF is used to perfom another action.

Is there an other action you would like to perform?

Regards,
Leon Bakkers

Peter van Eck SSC Enthusiast Points: 188 More actions · Answer 7

nope, sql authentication, no windows authentication.

Peter van Eck SSC Enthusiast Points: 188 More actions · Answer 8

well, for the first few weeks I would like to send me an e-mail, so I know when there is a delete action and on what time this happens, and all that..or at least log the action in some temp table so at least there's nothing deleted from this crucial table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

then you need to review the permissions granted to that username for that database and all server roles.

you say the problem is not there for new users.

how about deleteing and recreating the user.???

MVDBA

Peter van Eck SSC Enthusiast Points: 188 More actions · Answer 10

yep, that's exactly what I did..made sure this user has no rights at all ! but still didn't work, made your idea of recreating this user helps, but just to be sure : being member of the db_owner role/admin_role is not a reason why the permissions are overruled ?

tnx

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 11

Can you deny permissions to the dbo of a database? If you did, as dbo he could go back in and change the permission so he has the access he wants. If you want to deny this user delete permissions, I think you are going to have to make him something besides dbo.

HTH,

John

hoo-t SSChampion Points: 12713 More actions · Answer 12

hoo-t

SSChampion

Points: 12713

July 2, 2004 at 9:41 am

#513191

Change the owner of the database to "SA".

Steve