Re: Keeping User From Deleting Table Records

See my note where I point that NOT to implement on production.

"Hugo Kornelis" <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
On Mon, 14 Sep 2009 04:52:01 -0700, meangene wrote:

Uri - Yeah, on prod db this would keep our business app from deleting
when it needs to. Is there a way to make the trigger specific to user

Hi meangene,

I must admit that I'm surprised by the suggestion Uri made. Though it
will work, it's definitely not the recommended way to accomplish this.
It can also have several unwanted side effects, and there are more ways
to get around this than by setting appropriate permissions.

The only reason I see to use this is if you want to disable someone with
sysadmin priviledges from deleting rows, but:
a) anyone with sysadmin priviledges can circumvene anything you put in
their way; that's what sysadmin priviledge are for after all;
b) you shouldn't allow users or application to connect with such
elevated priviledges anyway.

Your question shows that your users don't use sysadmin priviledges, so
there is aboslutely no need to use triggers here. Just set up security
as recommended by Linchi..

Hugo Kornelis, SQL Server MVP
My SQL Server blog: