Revoke/deny SOX issue

Hi all,
Either revoke/deny doesnt work or I dont understand the concept correctly.
I need to separate the duties of SA and DBO for SOX
I am planning on creating 2 new roles in every database. Securityadmin and
DataAdmin.
In testing this I set myself up as a user of a test DB, as a member of
Public I can do everything in the DB as if I was SA.
I tried to deny all rights to my username, and to Public. After doing so I
was still able create insert, update, blah, blah...
I have tried all of the following:
DENY CREATE TABLE TO public
DENY SELECT, INSERT, UPDATE, DELETE
ON testrights
TO PUBLIC --username
REVOKE ALL ON testrights TO jfischer
I don't understand why I can still do everything on the server.
TIA,
JoeWhat does the following return
select is_srvrolemember('sysadmin')
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:F0EA0EB9-EF0D-460D-84D0-A1AF4E9F9307@.microsoft.com...
> Hi all,
> Either revoke/deny doesnt work or I dont understand the concept correctly.
> I need to separate the duties of SA and DBO for SOX
> I am planning on creating 2 new roles in every database. Securityadmin
> and
> DataAdmin.
> In testing this I set myself up as a user of a test DB, as a member of
> Public I can do everything in the DB as if I was SA.
> I tried to deny all rights to my username, and to Public. After doing so I
> was still able create insert, update, blah, blah...
> I have tried all of the following:
> DENY CREATE TABLE TO public
> DENY SELECT, INSERT, UPDATE, DELETE
> ON testrights
> TO PUBLIC --username
> REVOKE ALL ON testrights TO jfischer
> I don't understand why I can still do everything on the server.
> TIA,
> Joe|||Thank you!
The issue was I was part of the administrator group on the server. I didn't
realize the window account mattered. I thought SQL security was only
controled thru SQL.
Thanks again!
joe

Re-use SqlCommand object

Is it ok to re-use a SqlCommand object? In a method, I am executing 2 separate parameterized sql statements. Before I run the second, I will clear the command objects parameters.(command.parameters.clear()) I'm just checking to see if it is good coding practice or not.

thanks,

SC

Half the reason command objects exist actualy. Go right ahead!|||

I think it's OK to re-use a SqlCommand object, if you can make sure to finish using all objects associated with the SqlCommand before reusing it. However I won't suggest to reuse a SqlCommand for multiple purpose, it's better to keep each SqlCommand for single task and Dispose the objects after using it.

Returning Subsets of the same column in separate columns...

I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0

It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!

Caeanis

Hi, did you try this one here ?

SELECT SubQuery1.Counter,SubQuery2.Counter
FROM
(
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
) SubQuery1,
(
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
) SubQuery2

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de