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.