#askdba How to deny DBCC permisison to a user who is a owner of a database?
DBCC permissions are granted to the database and/or server roles, and from what I know, there is no way to deny them. Some of the commands require sysadmin role membership and nothing less. For example, to run dbcc dropcleanbuffers one must be a member of the sysadmin server role. To run dbcc freeproccache is allowed to the members of sysadmin or serveradmin server roes. dbcc showcontig requires a role membership to db_owner or db_ddladmin database roles, and of course sysadmin server role has the permission as well. Here is the script you can use to see which commands can be executed by which database and/or server role. I am sure that there are better scripts out there to see the same or similar results, but this one works:
drop table if exists #ServerOrDatabaseRoles; create table #ServerOrDatabaseRoles ( RoleName sysname not null, RoleScope varchar(8) null, CommandText varchar(128) not null ); go -- insert the database roles records insert into #ServerOrDatabaseRoles (RoleName, CommandText) exec sp_dbfixedrolepermission; -- delete all non-DBCC permissions and update the scope column delete #ServerOrDatabaseRoles where CommandText not like 'dbcc%'; update #ServerOrDatabaseRoles set RoleScope = 'Database'; -- insert the server roles records insert into #ServerOrDatabaseRoles (RoleName, CommandText) exec sp_srvrolepermission; -- delete all non-DBCC permissions and update the scope column delete #ServerOrDatabaseRoles where CommandText not like 'dbcc%'; update #ServerOrDatabaseRoles set RoleScope = 'Server' where RoleScope is null; select * from #ServerOrDatabaseRoles order by 3, 2; -- clean up drop table #ServerOrDatabaseRoles; go
The bottom line is that if the user is a member of the db_owner role then there is no way, from what I know, to deprive them from DBCC permissions which are granted to the role.
Hope this helps.
19 People are following this question.