Can an SQL Server application role issue DBCC DBREINDEX?

Question

I have an application that uses a SQL Server application role on the database side. During one of the application’s processes, I need it to reindex a table, however, I run into problems because apparently the application role doesn’t have permissions to run the DBCC DBREINDEX command. Do you know of a way to enable that?

Here’s the error that we’re seeing. Any advice would be appreciated.

User ‘UserX’ does not have permission to run DBCC DBREINDEX for object ‘TableX’.

I know DBCC DBREINDEX has been deprecated, but the application runs against a SQL Server 2005 instance and the database is SQL Server 2000 compatibility mode. The vendor of the application wants to keep the database in SQL Server 2000 compatibility mode because some of their queries don’t work in 2005. So just to be thorough, I get a similar error when trying to execute the ALTER INDEX statement.

Error #:-2147217900 - Cannot find the object "dbo.TableX" because it does not exist or you do not have permissions.

Answer 1

From BOL

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Change the rights. You could use a stored proc to wrap the DBCC and use EXECUTE AS to change context. Or even dynamic SQL. I'm not sure of how this works with App roles and compatibility mode, sorry.