3

I'm trying to deny all permissions on the dbo schema for a particular user, as I only want that user to access a specific schema. When I try the following (using sa):

DENY CONTROL, ALTER ON SCHEMA :: dbo TO AppUser

I get this message:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

Is it possible to deny all dbo schema permissions for a user?

EDIT: I've just had a thought - there are a few tables and views in the dbo schema that are owned by public (not my idea and I can't change them). Could this be why I am getting the message? If so, is there any way I can get around this? The purpose of denying access to dbo is to prevent unnecessary access to these tables.

EDIT: I think I got confused with the public role - the actual owner of the tables and views I mentioned is dbo, but the public role has SELECT, INSERT, UPDATE etc permissions. I've tried reproducing the message on a new database which has a table with public role permissions as in the other database, but this does not show the message - it is successful and the denied user can no longer see those tables. So to answer my question, yes it is possible, but I'm not sure why it isn't working for me.

Improve this question
6
  • 2
    The error message you're seeing is not reproducible in a new, blank database. Please create a minimal repro in code, and add that to your question via the edit button.
    – Hannah Vernon ♦
    Feb 13, 2017 at 14:55
  • @MaxVernon You're right - I've just tried reproducing it in a new database and it appears to work fine, despite a table being public. I'll have to keep digging, and see if I can find anything that may be causing the message. Thanks for your help - I should have done that first, but didn't think about it when I posted the question. At least I now know that it is possible.
    – Amy Barrett
    Feb 13, 2017 at 15:12
  • 1
    Uhmm, "The GRANT, DENY, and REVOKE statements have no effect on owners." according to this article - section default permissions. So the devil is in the details :).
    – Marian
    Feb 13, 2017 at 15:21
  • @Marian I've just updated the question - I checked again and the owner of those tables isn't actually public, it is dbo. The user I am trying to restrict is a different user. Thanks for the link though, it's worth knowing for future reference.
    – Amy Barrett
    Feb 13, 2017 at 15:36
  • Perhaps AppUser is the database owner?
    – Hannah Vernon ♦
    Feb 13, 2017 at 15:47

1 Answer 1

Reset to default
1

Restarting Management Studio fixed it.

It looks like it was some sort of glitch - for some reason, it was acting like I was using AppUser to run the query. I double checked and I was definitely logged in using sa, so I'm not sure what happened.

I realised when I tried to change permissions for the new schema, which had previously completed successfully and was now giving the same message. I tried to remove the login and just try setting it up again, and it gave the error that the user was still logged in, despite me closing all instances of Management Studio aside from the one I was currently using with the sa account. I tried to kill the session using the advice from this post, but it gave an error about not having permission (at this point I double checked yet again that I was definitely using the sa account). In the end I closed the current instance too, logged back in as sa, and it has worked fine since - I can grant/deny on the new schema and dbo for AppUser.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.