Sql-server – Deny dbo schema permission

access-controlpermissionsSecuritysql serversql-server-2008-r2

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):


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.

Best Answer

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.