Re: How to prevent DELETEs in a table

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/04/05 

Date: Mon, 3 Jan 2005 17:26:27 -0800

Hi Dave

If you are a member of the sysadmin role, permissions do not apply to you.
Basically, SQL Server just skips any permission validation for sysadmins. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dave" <dave@nospam.ru> wrote in message 
news:%23jxSZCf8EHA.2276@TK2MSFTNGP09.phx.gbl...
> To paraphrase Dr. Nick:
>
>    Thanks everybody.
>
> I like the idea of the trigger but I still do not understand why when I
> executed the "DENY DELETE ON EMPLOYEES TO public" I could still perform a
> DELETE.
>
> I always thought that a DENY tainted the well and since everyone was part 
> of
> public, everyone would be denied the ability to perform DELETEs.  But I
> still could.
>
> Permission changes take effect immediately in SQL Server don't they?
>
>
>
>
> "Anith Sen" <anith@bizdatasolutions.com> wrote in message
> news:ugO5UJe8EHA.3376@TK2MSFTNGP12.phx.gbl...
>> Do you want to prevent any deletions at all or do you want to prevent
>> certain users from deleting data? To prevent any deletions, create a
> INSTEAD
>> OF trigger on the table like:
>>
>> CREATE TRIGGER trg ON tbl INSTEAD OF DELETE
>> AS
>> IF @@ROWCOUNT > 0 ROLLBACK
>>
>> -- 
>> Anith
>>
>>
>
>

Relevant Pages

  • Re: Doub about ADP imlementation
    ... Yes I have the Allow Deletions set to true in the properties of the form. ... I don't believe that the problem is related with any type of permission on ... SQL Server or the connection to the SQL Server, because, I'm using ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Column Level Permissions Security Issue
    ... role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY ... When working with security in SQL Server it's imperative to understand ... discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html. ... there are users who have permission to access this ...
    (microsoft.public.sqlserver.security)
  • Re: SQL CLR Sproc isnt running right
    ... did you grant rights using the SQL Server 2005 Surface Area Config tool? ... and I got a permissions error back saying DBO doesn't have permission to insert records into the table... ... but the assemblie's stored procedures do all have execute permissions set for the role that the executing user is in... ... "William Vaughn" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • System.Security.SecurityException with .NET 2005 Only
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)
  • After 2003 to 2005 Upgrade Wizard, Getting System.Security.SecurityException
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)