Results 1 to 7 of 7

Thread: GRANT vs DENY

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Answered: GRANT vs DENY

    As part of recent permission changes I have this:
    Code:
    -- DENY I/U/D across the board
    DENY INSERT, UPDATE, DELETE
      TO report_writer
    ;
    This is lovely and I am most happy with it...


    ...until I find an exception!

    I need to grant CRUD permissions to a single object, but obviously DENY beats GRANT.

    Is there any workaround?
    George
    Home | Blog

  2. Best Answer
    Posted by Pat Phelan

    "I'm generally in favor of losing your mind, it has always worked well for me.

    From a query window, execute:
    Code:
    EXEC xp_logininfo 'MyDomain\MyADLogin', 'all'
    The output will show all of the SQL Server logins that apply to a given Windows Authenticated login, and more importantly in which order the SQL logins are applied to determine permissions.

    -PatP"


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I assume this is after granting CONTROL permissions on the schema? If not, then it is easy. Permissions default closed, so you don't need to have the DENY statements, and only grant update permissions on the items they need to update.

  4. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, a single DENY will trump an infinite number of GRANTS.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Didn't bother with CONTROL in the end. I'll have to check the permissions on the public role (I've had people f*** with that one before, sadly) and see if I can get away with omitting the initial DENY.
    Cheers doods.
    George
    Home | Blog

  6. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How very odd...

    AD group added as a login and is assigned the new role.

    Added my test account in to the AD group, tested the new perms - brilliant, they work!
    Got one of my users to try it out on their login: CRUD permission denied!

    I'm a little bit baffled! Going to trawl through and see if the test user gains any other permissions from elsewhere, but I am doubting it considering they couldn't log in before I added them to the AD group!

    Any ideas before I lose my mind?
    George
    Home | Blog

  7. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm generally in favor of losing your mind, it has always worked well for me.

    From a query window, execute:
    Code:
    EXEC xp_logininfo 'MyDomain\MyADLogin', 'all'
    The output will show all of the SQL Server logins that apply to a given Windows Authenticated login, and more importantly in which order the SQL logins are applied to determine permissions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: Aha!! At some point the troublesome user has been added by name. The login was tidied up but not the user. I think that's us sorted

    Thanks Pat. Your post got me over the line
    Last edited by gvee; 02-19-15 at 12:56.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •