I ran into a problem with a DBCC command recently. It involved an application running index maintenance on a daily basis. I can appreciate what the software vendor was trying to do. They probably sell their product to a lot of organizations that don't have a SQL Server DBA on staff. So they baked in some index maintenance because they thought it was the right thing to do.
None to my surprise, it was implemented poorly. The app would iterate through every table in the database and run DBCC DBREINDEX ('<table>'), forcing a rebuild of every index whether it was necessary or not. That's a sledgehammer approach I wouldn't choose.
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.
Here's a script with my testing. It creates a test database, a table, an index on the table, and a database user without a login. The user is added to the db_ddladmin database role and denied the ALTER permission on the one table.
--Create test DB and one test table with an index. IF DB_ID('DbccTest') IS NULL BEGIN CREATE DATABASE DbccTestDB; SELECT TOP(100) * INTO DbccTestDB.dbo.TempObjects FROM DbccTestDB.sys.objects; CREATE INDEX IDX_TempObjects_Name ON DbccTestDB.dbo.TempObjects(name); END GO USE DbccTestDB; GO IF NOT EXISTS ( SELECT * FROM sys.database_principals p WHERE p.name = 'TestUser' ) CREATE USER TestUser WITHOUT LOGIN GO ALTER ROLE db_ddladmin ADD MEMBER TestUser; DENY ALTER ON dbo.TempObjects TO TestUser; GO EXECUTE AS USER = 'TestUser'; --Success DBCC DBREINDEX ( 'dbo.TempObjects', IDX_TempObjects_Name, 90 ) WITH NO_INFOMSGS; GO --Failure ALTER INDEX IDX_TempObjects_Name ON dbo.TempObjects REBUILD; GO REVERT; GO
I tried some different DENY combinations on the user, but as long as the user was a member of db_ddladmin, nothing I tried would stop DBCC REINDEX...except for DENY CONNECT. But that throws baby out with the bath water. Any further activity with the related database user results in this error:
Msg 916, Level 14, State 1, Line 1 The server principal "S-1-9-3-2650503887-1101458569-1393957563-2948224095." is not able to access the database "DbccTestDB" under the current security context.
I hope I am wrong: my conclusion is that you can't "turn off" access to DBCC, other than removing membership from fixed database roles (or the sysadmin fixed server role). While that's often a wise thing to do from a security perspective, it often breaks application functionality. Ideally, I'd like Microsoft to provide a DENY DBCC option. It sounds simple enough, but I don't write the SQL Server database engine code.
MSDN tells us that DBCC REINDEX "will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead." That's classic non-committal verbiage. It doesn't help now and "a future version" is too vague. It looks like I'm stuck. I can't cage the DBCC beast without breaking the zoo. The maulings will continue until morale improves.