Re: Keeping User From Deleting Table Records



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

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


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

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: http://sqlblog.com/blogs/hugo_kornelis


.



Relevant Pages

  • Re: DBA Permissions
    ... I don't have a windows account, ... account with sysadmin priviledges.. ... >Columnist, SQL Server Professional ... >which is in the local administrators group and I am able ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... on prod db this would keep our business app from deleting records ... Is there a way to make the trigger specific to user logins? ... I must admit that I'm surprised by the suggestion Uri made. ... sysadmin priviledges from deleting rows, ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... I've decided to work on setting up security based on user permissions through ... sysadmin priviledges from deleting rows, ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.security)