I receive SQL Server exception reports, filtered on exceptions based on severity. I noticed a high level of exceptions with the pattern:
User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'.
Error = 7983
Severity = 14
State = 36
I ran a sql server trace and spotted the exception when the login attempted to execute the the command:
dbcc opentran ("myDB") with tableresults
It was monitoring software running regularly on the SQL Server. I wasn’t surprised the software was attempting to execute this command.
I checked through the documentation and confirmed I’d set up the login security as documented based on the monitoring profile I wanted to use.
According to MSDN Online “DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database”
DBCC OPENTRAN requires membership in the sysadmin fixed server role or the db_owner fixed database role.
It was against the SQL Server security policy to allow sysadmin privileges and db_owner.
Upon analysis, the monitoring team confirm the module should not have been turned on . Once they turned off the module , the User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'. exception disappeared