Re: How to prevent DELETEs in a table
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/04/05
- Next message: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Previous message: James-Aus: "Log Shipping and SQL Server Licence Requirement"
- In reply to: AnthonyThomas: "Re: How to prevent DELETEs in a table"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 3 Jan 2005 21:28:02 -0800
What you say about dbo is true, but it is even MORE true about someone who
is in the sysadmin role. No permissions are ever checked for someone in the
sysadmin role. For dbo users, there is some checking of permissions, but
because dbo is the owner, the deny is not applied. It is a different
internal mechanism.
In older versions, you could deny access to dbo, and a dbo who wasn't a
sysadmin would actually lose privilege even on objects owned by dbo.
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "AnthonyThomas" <Anthony.Thomas@CommerceBank.com> wrote in message news:eHyFhpg8EHA.2016@TK2MSFTNGP15.phx.gbl... > 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 >>> >>> >> >> > >
- Next message: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Previous message: James-Aus: "Log Shipping and SQL Server Licence Requirement"
- In reply to: AnthonyThomas: "Re: How to prevent DELETEs in a table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
