Preventing DBCC DBREINDEX?

Kevin Feasel

2016-11-23

DBCC

Dave Mason is looking for a way to prevent a user from running DBCC REINDEX:

After a little digging, I discovered the related database user is a member of the db_ddladmin fixed database role. Members of that role are permitted to run DBCC REINDEX. Since I have existing (more sensible) code in place for index maintenance, I don’t want the DBCC REINDEX operations to continue. Here’s the problem: I can’t find a direct way to DENY a database user from running DBCC commands. T-SQL syntax doesn’t support something like DENY DBCC TO <user> or DENY DBCC REINDEX TO <user>. MSDN documentation tells me the equivalent ALTER INDEX command requires at minimum ALTER permission on the table or view. I guessed that revoking or denying ALTER TABLE privileges might prevent a user from executing DBCC DBREINDEX, but that does not appear to be the case.

That’s painful.

Related Posts

Diagnosing Database Corruption

Jeff Mlakar has started a series on database corruption.  His first post involves finding corruption: The cause lies in layers below SQL Server. The most common are hardware faults; in particular, issues with the I/O Subsystem. Any component in the I/O Subsystem can fail and be the cause of database corruption: disks, controllers, CPU, memory, […]

Read More

How Far Along Is That CHECKDB?

Kevin Feasel

2018-06-06

DBCC

Steve Stedman shows us how to get percentage complete of a DBCC CHECKDB run: You have a DBCC CHECKDB script running, something like the following, and it may take several hours to run to confirm if there is any corruption in your SQL Server Database. 1 DBCC CHECKDB ('MyDatabaseName') WITH DATA_PURITY, NO_INFOMSGS; Then someone asks […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930