I’m trying to lock down an audit table in our database. As a test, I opene
d the table’s ‘manage permissions’ dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database
role membership and saw that she was a member of the db_owner role, so I rev
oked that. I then ran a DENY statement: "deny delete on Histories to edenr".
I removed her memberships in the db_accessadmin and db_securityadmin roles,
and had her close and reop
en Enterprise Manager. After all that, she was still able to delete records.
The manage permissions dialog for this table shows that she is denied delete
permissions. She is still a member of the public, db_datareader, and db_dat
awriter groups, but that shouldn’t override explicitly denied permissions.
I’m the dbo of the datab
ase, so I certainly should have sufficient rights to issue a denial.
What does it TAKE to block a programmer from having permission to delete rec
ords?Yes but what Login is Enterprise Manager using? It is probably not hers.
Andrew J. Kelly SQL MVP
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Hi,
Check the role associated for the user first by executing below command:-
sp_helplogins <Login_name_for that _user'
If you have any roles apart from db_datareader and db_datawriter revoke
that.
After this Execute the below command
use <dbname>
go
deny delete on <table_name> to <user_name>
After that login to query analyzer using that user and run the command:-
select suser_sname()
Now execute the delete statatement on that table.
Thanks
Hari
MCDBA
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Thanks for the suggestions. I tried this, and got the same result. It did ha
ve the effect of re-confirming that the deletions were being run under the p
ermissions of the user in question, which was useful.
The goal here is to be able to block anybody, including programming team mem
bers, from being able to delete records in the production database's audit t
able.
Got any other suggestions where she might be getting delete permissions that
override the explicit denial?
"Hari" wrote:
> Hi,
> Check the role associated for the user first by executing below command:-
> sp_helplogins <Login_name_for that _user'
> If you have any roles apart from db_datareader and db_datawriter revoke
> that.
> After this Execute the below command
> use <dbname>
> go
> deny delete on <table_name> to <user_name>
> After that login to query analyzer using that user and run the command:-
> select suser_sname()
> Now execute the delete statatement on that table.
>|||Check server roles as well. Maybe she is a member of
sysadmins either directly or through windows group
membership
-Sue
On Fri, 2 Jul 2004 09:07:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the suggestions. I tried this, and got the same result. It did h
ave the effect of re-confirming that the deletions were being run under the
permissions of the user in question, which was useful.
>The goal here is to be able to block anybody, including programming team me
mbers, from being able to delete records in the production database's audit
table.
>Got any other suggestions where she might be getting delete permissions tha
t override the explicit denial?
>"Hari" wrote:
>|||Thanks--it looks like that was it. Most of our programmers, including the on
e I'm using as a test case, are members of the System Adminstrators role, an
d the System Adminstrators role has delete permissions on any object in any
database.
All domain admins are automatically members of the sysadmins role, so anyone
who is a domain admin can't be removed from the group even if I decided tha
t was the best solution.
It looks like permissions granted due to membership in the sysadmins role ca
n't be overridden by a denial? Is there any way to override these permission
s in a particular database?
"Sue Hoegemeier" wrote:
> Check server roles as well. Maybe she is a member of
> sysadmins either directly or through windows group
> membership|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.sqlserver.security
NNTP-Posting-Host: 0-1pool76-99.nas29.thornton1.co.us.da.qwest.net 67.4.76.9
9
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.security:21673
Good - glad to hear that helped you track it down.
On the sysadmins, someone who is a member of the role can do
everything. Members of this role bypass any denies you set
up for them. You can't override this on any level, not by
database or anything else. They can do whatever.
Regarding domain admins, they get their access through the
BUILTIN\Administrators group in SQL Server that is by
default a member of sysadmins. You can remove the
BUILTIN\Administrators but doing this can cause some
problems. Whether you get problems or not depends. The
following article has an more information section with links
to some issues that could come up:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Tue, 6 Jul 2004 11:38:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks--it looks like that was it. Most of our programmers, including the o
ne I'm using as a test case, are members of the System Adminstrators role, a
nd the System Adminstrators role has delete permissions on any object in any
database.
>All domain admins are automatically members of the sysadmins role, so anyon
e who is a domain admin can't be removed from the group even if I decided th
at was the best solution.
>It looks like permissions granted due to membership in the sysadmins role c
an't be overridden by a denial? Is there any way to override these permissio
ns in a particular database?
>"Sue Hoegemeier" wrote:
>
