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

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.

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.

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

Sql-server – Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

If all data access is via stored procedures, then permissions on the table are not checked of the same user (dbo probably) owns both procedure and tables. This is called ownership chaining

This also means the no permissions are needed on the tables at all. You don't need to deny or grant anything on the tables because they won't be checked. Lack of GRANT means no rights anyway, so you don't need DENY

Now, this means nothing to folk with elevated privileges: sysadmin, db_owner etc. These will always have rights. You can only use triggers to block these: but they can disable or drop triggers of course.

I assume that your "end users" are not running as db_owner or sysadmin...

Best Answer

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Sql-server – Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

Related Question