Error 7983 - User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'

20 May,2015 by Jack Vamvas

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

Read More  on SQL Server Profiler and Exception based reporting

SQL Server profiler Scan Event - SQL Server DBA

Exception based reporting - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Error 7983 - User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer