Permissions Grant-Deny-Other

From: Aaron Barkel (aaron_bar@hotmail.com)
Date: 03/28/03 

From: "Aaron Barkel" <aaron_bar@hotmail.com>
Date: Fri, 28 Mar 2003 09:23:12 -0600

I have been reading up on Deny priveleges in BOL, but I need to make certain
I am interpreting the text correctly.

Consider the given scenario:

DB: Test
Tables: TestA & TestB
Roles: Public
Users: User1DBA, User2Other
StoredProcedures: sp_A (simple select from TableA, created by dbo), sp_B
(simple select from TableB, created by dbo)

Both User are given access to the DB Test.

User1DBA has all allowed priveleges on the server (Sys Admin, Sec Admin,
etc.) and all allowed priveleges on the DB (public, db_Owner,
db_AccessAdmin, etc)
User2Other has no priveleges assigned on the server and only public on the
DB.

Table TestA has priveleges set so that public is granted select and table
TestB has priveleges set so that public is explicitly denied select.

User1DBA is given exec priveleges on both stored procedures.
User2Other is given exec priveleges on both stored procedures.

T or F
1) User1DBA will always be able to select from both tables?
2) User2Other will always be able to select from TableA, but will never be
able to select from TableB?
3) User1DBA will always be able to select from both stored procedures?
4) User2Other will be able to return a recordset from sp_A, but will be
denied from returning a recordset from sp_B?

Now consider the above with one change:
Table TestA has priveleges set so that public is granted select and table
TestB has priveleges set so that public is neither explicitly denied select
or explicitly granted select.

T or F
5) User1DBA will always be able to select from both tables?
6) User2Other will always be able to select from TableA, but will never be
able to select from TableB?
7) User1DBA will always be able to select from both stored procedures?
8) User2Other will be able to return a recordset from sp_A, but will NOT be
denied from returning a recordset from sp_B?

I know this may seem rudimentary to some of the DBAs here, but I need to
verify that I am correctly structuring things.

Thanx all in advance,

A

Relevant Pages

  • Re: Permissions Grant-Deny-Other
    ... it makes sense for the server to assume that when you grant ... > User1DBA has all allowed priveleges on the server (Sys Admin, Sec Admin, ... > User2Other has no priveleges assigned on the server and only public on the ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions Grant-Deny-Other
    ... User1DBA is sysadmin so no priviliges are checked ever, ... > Users: User1DBA, User2Other ... > User1DBA has all allowed priveleges on the server (Sys Admin, Sec Admin, ... > User2Other is given exec priveleges on both stored procedures. ...
    (microsoft.public.sqlserver.security)