Re: How to prevent DELETEs in a table

From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 01/04/05 

Date: Mon, 3 Jan 2005 21:05:16 -0600

This is close, but not quite accurate. Basically, dbo is THE database
owner. It is the dbo database USER, not server-level groups, that determins
the effect of database permissions.

Now, what is interesting is that dbo can not be denied anything...because it
owns everything. It has implicit permissions that can not be denied.
Furthermore, as a member of the system administrator system role, users are
atomactically aliased to the dbo user in every database hosted by the
system.

Conversely, members of the db_owner database role can be denied access
because they have only inherited permissions through grants that CAN be
denied: DENY CREATE TABLE, etc., and DENY SELECT, INSERT, UPDATE, DELETE,
etc. What they can do, however, is regrant themselves these permissions.

This situation is akin to the relationship between the SYSTEM and Local
Administrators accounts/groups for the Operating System. Think of DBO as
SYSTEM and db_owner members like Local Administrators. The parallels are
strikingly close.

Sincerely,

Anthony Thomas 

-- 
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:%239nxsxf8EHA.3368@TK2MSFTNGP10.phx.gbl...
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

  • Problem is w/ .ADP..Re: SQL db Permissions for users not working
    ... You do not have SELECT permissions on the ... SysObjects system table in the database. ... figured out that qualifying the database owner (dbo in my ... >> I feel that the object owner is not dbo, ...
    (microsoft.public.sqlserver.security)
  • RE: copy permissions from one user to another?
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- ADD USER TO SERVER ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE ...
    (microsoft.public.sqlserver.security)
  • Re: Effective Permissions Error with Domain User
    ... I set the database compatibility to 2005. ... server profile trace and found that it was calling the Execute As User. ... This leads me to believe it is some sort of permissions issue. ... Did you get these database from SQL Server 2000 by using a RESTORE command? ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
    (microsoft.public.sqlserver.server)
  • Re: Security Problem with AD Group in SQL Server Security Logins area
    ... role they were mapped to the dbo user in each database. ... Jasper Smith (SQL Server MVP) ... >> system role of "System Administrators." ...
    (microsoft.public.sqlserver.security)