Rights to start SQL services

Hi all,
In my ongoing PAIN of SOX, I am in the middle of SA DBA split of duties.
I have 2 different accounts that start my SQL Services, I have one account
that starts SQL server service and another to start SQL agent. I know these
accounts need to have SA rights to the server. Is there a way to revoke
rights in the individual databases? As SA even if I maunaully revoke the
delete rights to a table I can still log on as them and delete records.
TIA,
JoeYou can't deny rights in SQL Server to a member of
sysadmins. Sysadmin members can perform any action in SQL
Server and bypass any denies you may set up.
-Sue
On Mon, 1 Aug 2005 09:01:03 -0700, jaylou
<jaylou@.discussions.microsoft.com> wrote:

>Hi all,
>In my ongoing PAIN of SOX, I am in the middle of SA DBA split of duties.
>I have 2 different accounts that start my SQL Services, I have one account
>that starts SQL server service and another to start SQL agent. I know thes
e
>accounts need to have SA rights to the server. Is there a way to revoke
>rights in the individual databases? As SA even if I maunaully revoke the
>delete rights to a table I can still log on as them and delete records.
>TIA,
>Joe

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