question

baralshakti12 avatar image
baralshakti12 asked

How to deny DBCC permisison to a user who is a owner of a database?

#askdba How to deny DBCC permisison to a user who is a owner of a database?

dba
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered

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.

Oleg

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.