can't block delete permissions

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:
>

Cannot resolve collation conflict for equal to operation.

I'm trying to audit our sql server and getting a collation error. "Cannot
resolve collation conflict for equal to operation." I know 3 of the
databases on this server have a different collation, but I need to find a wa
y
to get around this since I can't change the collation because they are vendo
r
databases. Does anyone have any ideas? I'm at a loss and this is due to our
auditors within the next few days. Thanks in advance for your help! This is
the code I'm using to audit:
DECLARE @.spname VARCHAR (128)
DECLARE @.dbname VARCHAR (128)
DECLARE @.logiNAME VARCHAR (2000)
DECLARE @.cmd VARCHAR (5000)
-- Declare cursor
DECLARE sp_csr INSENSITIVE CURSOR FOR
select name as dbname from sysdatabases
order by name
-- Open the cursor
OPEN sp_csr
-- Loop through all the databases in the database
FETCH NEXT
FROM sp_csr
INTO @.dbname
print @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'Use ' + @.dbname + ' '+ 'SELECT ''ROLE NAME'' = b.name, 30,
''OBJECT NAME'' = c.name, 30,
''ACTION'' = CASE a.action
WHEN 26 THEN ''REFERENCES''
WHEN 193 THEN ''SELECT''
WHEN 195 THEN ''INSERT''
WHEN 196 THEN ''DELETE''
WHEN 197 THEN ''UPDATE''
WHEN 198 THEN ''CREATE TABLE''
WHEN 203 THEN ''CREATE DATABASE''
WHEN 204 THEN ''GRANT_W_GRANT''
WHEN 205 THEN ''GRANT''
WHEN 206 THEN ''REVOKE''
WHEN 207 THEN ''CREATE VIEW''
WHEN 222 THEN ''CREATE PROCEDURE''
WHEN 224 THEN ''EXECUTE''
WHEN 228 THEN ''DUMP DATABASE''
WHEN 233 THEN ''CREATE DEFAULT''
WHEN 235 THEN ''DUMP TRANSACTION''
WHEN 236 THEN ''CREATE RULE''
END,
"TYPE" = CASE c.type
WHEN ''C'' THEN ''CHECK constraint''
WHEN ''D'' THEN ''Default or DEFAULT constraint''
WHEN ''F'' THEN ''FOREIGN KEY constraint''
WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
WHEN ''L'' THEN ''Log''
WHEN ''P'' THEN ''Stored procedure''
WHEN ''R'' THEN ''Rule''
WHEN ''RF'' THEN ''Stored procedure for replication''
WHEN ''S'' THEN ''System table''
WHEN ''TR'' THEN ''Trigger''
WHEN ''U'' THEN ''User table''
WHEN ''V'' THEN '' View''
WHEN ''X'' THEN ''Extended stored procedure''
WHEN ''FN'' THEN ''User Defined Function''
END,
ServerName=(select srvname from master..sysservers where srvid = ''0''),
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename =
master..sysdatabases.filename)
, Environment = Case when (select srvname from master..sysservers where
srvid = ''0'') like ''Dev%''
THEN ''DEV''
WHEN (select srvname from master..sysservers where srvid = ''0'') like
''Prod%''
THEN ''PROD''
WHEN (select srvname from master..sysservers where srvid = ''0'')LIKE
''Test%''
THEN ''TEST''
ELSE ''Unknown''END
FROM sysprotects a, sysusers b, sysobjects c
WHERE a.uid = b.uid
AND c.id = a.id
--AND b.name = @.group
ORDER BY b.name, c.name, a.action'
--PRINT @.cmd
EXEC (@.cmd)
FETCH NEXT
FROM sp_csr
INTO @.dbname
END
-- Close and deallocate the cursor
CLOSE sp_csr
DEALLOCATE sp_csrat a glance, i'm not seeing anything jump out - first step would be
capturing the @.cmd that errored, run it separately and narrow down which
exact part is the problem.
It is possible to coerce a query to use a specified collation - after
the criteria. e.g.
...from table1 join table2 on table1.column = table2.column collate
<collation>...
...from table1 where column='some value' collate <collation>
Anita wrote:

>I'm trying to audit our sql server and getting a collation error. "Cannot
>resolve collation conflict for equal to operation." I know 3 of the
>databases on this server have a different collation, but I need to find a w
ay
>to get around this since I can't change the collation because they are vend
or
>databases. Does anyone have any ideas? I'm at a loss and this is due to our
>auditors within the next few days. Thanks in advance for your help! This is
>the code I'm using to audit:
>DECLARE @.spname VARCHAR (128)
>DECLARE @.dbname VARCHAR (128)
>DECLARE @.logiNAME VARCHAR (2000)
>DECLARE @.cmd VARCHAR (5000)
>
>-- Declare cursor
>DECLARE sp_csr INSENSITIVE CURSOR FOR
>select name as dbname from sysdatabases
>order by name
>-- Open the cursor
>OPEN sp_csr
>-- Loop through all the databases in the database
>FETCH NEXT
> FROM sp_csr
> INTO @.dbname
> print @.dbname
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> SELECT @.cmd = 'Use ' + @.dbname + ' '+ 'SELECT ''ROLE NAME'' = b.name, 30,
>''OBJECT NAME'' = c.name, 30,
>''ACTION'' = CASE a.action
>WHEN 26 THEN ''REFERENCES''
>WHEN 193 THEN ''SELECT''
>WHEN 195 THEN ''INSERT''
>WHEN 196 THEN ''DELETE''
>WHEN 197 THEN ''UPDATE''
>WHEN 198 THEN ''CREATE TABLE''
>WHEN 203 THEN ''CREATE DATABASE''
>WHEN 204 THEN ''GRANT_W_GRANT''
>WHEN 205 THEN ''GRANT''
>WHEN 206 THEN ''REVOKE''
>WHEN 207 THEN ''CREATE VIEW''
>WHEN 222 THEN ''CREATE PROCEDURE''
>WHEN 224 THEN ''EXECUTE''
>WHEN 228 THEN ''DUMP DATABASE''
>WHEN 233 THEN ''CREATE DEFAULT''
>WHEN 235 THEN ''DUMP TRANSACTION''
>WHEN 236 THEN ''CREATE RULE''
>END,
>"TYPE" = CASE c.type
>WHEN ''C'' THEN ''CHECK constraint''
>WHEN ''D'' THEN ''Default or DEFAULT constraint''
>WHEN ''F'' THEN ''FOREIGN KEY constraint''
>WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
>WHEN ''L'' THEN ''Log''
>WHEN ''P'' THEN ''Stored procedure''
>WHEN ''R'' THEN ''Rule''
>WHEN ''RF'' THEN ''Stored procedure for replication''
>WHEN ''S'' THEN ''System table''
>WHEN ''TR'' THEN ''Trigger''
>WHEN ''U'' THEN ''User table''
>WHEN ''V'' THEN '' View''
>WHEN ''X'' THEN ''Extended stored procedure''
>WHEN ''FN'' THEN ''User Defined Function''
>END,
>ServerName=(select srvname from master..sysservers where srvid = ''0''),
>DatabaseName=(SELECT master..sysdatabases.name
>FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename =
>master..sysdatabases.filename)
>, Environment = Case when (select srvname from master..sysservers where
>srvid = ''0'') like ''Dev%''
> THEN ''DEV''
> WHEN (select srvname from master..sysservers where srvid = ''0'') like
>''Prod%''
> THEN ''PROD''
> WHEN (select srvname from master..sysservers where srvid = ''0'')LIKE
>''Test%''
> THEN ''TEST''
> ELSE ''Unknown''END
>FROM sysprotects a, sysusers b, sysobjects c
>WHERE a.uid = b.uid
>AND c.id = a.id
>--AND b.name = @.group
>ORDER BY b.name, c.name, a.action'
>
> --PRINT @.cmd
>EXEC (@.cmd)
> FETCH NEXT
> FROM sp_csr
> INTO @.dbname
>END
>
>-- Close and deallocate the cursor
>CLOSE sp_csr
>DEALLOCATE sp_csr
>|||Change
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename =
master..sysdatabases.filename)
to
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename COLLATE
Latin1_General_CI_AS =
master..sysdatabases.filename COLLATE Latin1_General_CI_AS)|||That worked as far as getting those databases that have a different
collation's info, but I'm still getting errors. Now I'm getting these errors
:
Line 41: Incorrect syntax near 'COLLATE'.
Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'like'.
Thanks!
"markc600@.hotmail.com" wrote:

> Change
> DatabaseName=(SELECT master..sysdatabases.name
> FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename =
> master..sysdatabases.filename)
> to
> DatabaseName=(SELECT master..sysdatabases.name
> FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename COLLATE
> Latin1_General_CI_AS =
> master..sysdatabases.filename COLLATE Latin1_General_CI_AS)
>