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

CHECKDB On Azure SQL Database

Arun Sirpal ponders running DBCC CHECKDB on Azure SQL Database: I was exchanging messages with Azure Support and even though I didn’t get a concrete answer to confirm this I ended up asking the question within a Microsoft based yammer group and yes they do automatically carry out consistency checks. This is great but it […]

Read More

When CHECKDB Snapshots Run Out Of Disk Space

Andy Galbraith walks through an error message in DBCC CHECKDB when the snapshot runs out of disk space: Looking in the SQL Error Log there were hundreds of these combinations in the minutes immediately preceding the job failure: — The operating system returned error 665(The requested operation could not be completed due to a file […]

Read More

Categories

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