**Deny**

Hi
I am working with SQL 2000 and I want to prevent some users of some
editing action and I could successfully do it by following statement,
DENY SELECT, INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
Now, I want to know how can I automatically deny some new tables or views
which will be created after the time I performed Deny command. Mary, John,
Tom are all db_owner and automatically will be granted to all actions when
a new table created, but I want to perform above command automatically for
each new table which begins with 'au', How is it possible?
I would be grateful if you help me.Hi
If those users are not members of dbo_owner database role so they would not
have an access to the new object created as dbo or other user
<R> wrote in message news:ops7g4arqwmw7tkz@.system109.parskhazar.net...
> Hi
> I am working with SQL 2000 and I want to prevent some users of some
> editing action and I could successfully do it by following statement,
> DENY SELECT, INSERT, UPDATE, DELETE
> ON authors
> TO Mary, John, Tom
> Now, I want to know how can I automatically deny some new tables or views
> which will be created after the time I performed Deny command. Mary, John,
> Tom are all db_owner and automatically will be granted to all actions when
> a new table created, but I want to perform above command automatically for
> each new table which begins with 'au', How is it possible?
> I would be grateful if you help me.|||DENY is only needed is when you want to negate a previous GRANT and is
usually done when permissions are inherited via role membership. However,
db_owner is a special role that allows members to have the same permissions
as the object owner. Since permissions are not checked for object owners,
GRANT and DENY have no affect on db_owner role members.
The bottom line is that Mary, John and Tom should not be db_owner role
members if you need to restrict their permissions in the database.
Hope this helps.
Dan Guzman
SQL Server MVP
<R> wrote in message news:ops7g4arqwmw7tkz@.system109.parskhazar.net...
> Hi
> I am working with SQL 2000 and I want to prevent some users of some
> editing action and I could successfully do it by following statement,
> DENY SELECT, INSERT, UPDATE, DELETE
> ON authors
> TO Mary, John, Tom
> Now, I want to know how can I automatically deny some new tables or views
> which will be created after the time I performed Deny command. Mary, John,
> Tom are all db_owner and automatically will be granted to all actions when
> a new table created, but I want to perform above command automatically for
> each new table which begins with 'au', How is it possible?
> I would be grateful if you help me.|||Even if you could deny these people access, if they are in the db_owner
role, they could just grant the rights back to themselves!
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OC6CJg9VGHA.1160@.TK2MSFTNGP09.phx.gbl...
> DENY is only needed is when you want to negate a previous GRANT and is
> usually done when permissions are inherited via role membership. However,
> db_owner is a special role that allows members to have the same
> permissions as the object owner. Since permissions are not checked for
> object owners, GRANT and DENY have no affect on db_owner role members.
> The bottom line is that Mary, John and Tom should not be db_owner role
> members if you need to restrict their permissions in the database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <R> wrote in message news:ops7g4arqwmw7tkz@.system109.parskhazar.net...
>