Records don't get deleted despite user having correct rights

Question

Records don't get deleted despite user having correct rights

Resender

SSCertifiable

Points: 7443
More actions
May 19, 2014 at 2:42 am

#293467

I got the following problem:
Last week I gave db_datareader,db_datawriter role to a user/login on a specific database. (User also has public role).
Now I'm getting reports that the this user can execute delete statements & get's the message Row(s) affected, with the correct number deleted.
But the records aren't deleted.
Updating the records works fine.
Anyone got any idea what this could be caused by (No I don't have specifics which table it is, but I did check their is no triggers in the database at all)

Viewing 8 posts - 1 through 7 (of 7 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Can you post exact error while deleting records?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

dbalmf SSCrazy Points: 2063 More actions · Answer 2

Is there any BEGIN TRANSACTION.... ROLLBACK in the error handling, indeed are they hitting the error trap for some reason

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

dbalmf (5/19/2014)
Is there any BEGIN TRANSACTION.... ROLLBACK in the error handling, indeed are they hitting the error trap for some reason

This was my first thought too, but also is there a trigger preventing deletes?

Resender SSCertifiable Points: 7443 More actions · Answer 4

We found the issue apparently the program that was running the delete statements with that user reinserted the same records it just deleted.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

Beatrix Kiddo

SSC-Dedicated

Points: 32407

May 19, 2014 at 7:06 am

#1714602

😀 You couldn't make it up.

Resender SSCertifiable Points: 7443 More actions · Answer 6

Indeed cause the funny thing was this behavior in the program was only triggered when it ran/runs on schedule,when they manually ran the program it did not occur.

SO their were pointing the finger at the sql side,only after I decided to put a trace on the user/database used did I notice,hold on why is it reinserting these just after deleting them.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Look for triggers on the underlying tables. For example, some locations prefer so-called "soft deletes", where the row is never actually deleted, but instead a "deleted" flag gets set. [Terrible idea for performance, btw.]

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".