Re: Revoke/deny SOX issue

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 07/20/05 

Date: Wed, 20 Jul 2005 22:38:52 +0100

What 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

Relevant Pages

  • Re: Column Level Permissions Security Issue
    ... role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY ... When working with security in SQL Server it's imperative to understand ... discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html. ... there are users who have permission to access this ...
    (microsoft.public.sqlserver.security)
  • Re: Groups VS Users
    ... > The short question is why does SQL Server seems to deny access to a user ... Each group as slightly different permissions. ... access denied ACE will override all access allowed ACEs for the same ...
    (microsoft.public.sqlserver.security)
  • Re: How do I find I am administrator?
    ... Procedure" and "Create View" for your SQL server user using the ... DENY statement; ... In this case even if you are db_owner for a database you will not able to do ...
    (microsoft.public.sqlserver.connect)
  • Re: How do I find I am administrator?
    ... Procedure" and "Create View" for your SQL server user using the ... DENY statement; ... In this case even if you are db_owner for a database you will not able to do ...
    (microsoft.public.sqlserver.server)
  • Re: How do I find I am administrator?
    ... Procedure" and "Create View" for your SQL server user using the ... DENY statement; ... In this case even if you are db_owner for a database you will not able to do ...
    (microsoft.public.sqlserver.tools)