Dynamics Community Forum Thread Details

SHOWPLAN permission denied in SQL Trace

When trying to use the trace functionality in Dynamics AX 2009, I am receiving an error that crashes the AOS in production. The error message on the client, server trace log, and system eventlog point to the same error:

SQL error description: [Microsoft][SQL Native Client][SQL Server]SHOWPLAN permission denied in database 'AxProd'.

In the development environment I have no problem using trace functionality in AX to get an execution plan when the appropriate thresholds are met.

The AOS service account is 'Network Service' on both development and production

Does anyone know what the problem might be ?

A difference between SQL Server security between the environment shows that the 'NT AUTHORITY\SERVCE ACCOUNT' is listed in the Login node, and Users Nodes in the development environment, but not in the production environment , which is perplexing.

I am assuming that it is the service account executing the query on the database.

Call stack with the error is as follows:

SQL error code: 262

Call stack:

(S)\Classes\ResultSet\next

(S)\Classes\SysSqlShowPlan\packMSSqlPlan - line 122

(S)\Classes\SysSqlShowPlan\packPlan - line 16

(S)\Classes\SysTraceSql\new - line 13

(S)\Classes\SysTraceSql\newSysTraceSql - line 10

(S)\Classes\Application\sysTrace - line 35

(C)\Classes\QueryRun\next

(C)\Reports\CustSalesOpenLinesByItem_NA\Methods\fetch - line 8

(C)\Classes\ReportRun\run

(C)\Classes\SysReportRun\run - line 26

(C)\Classes\RunBaseReport\run - line 34

(C)\Classes\SysReportRun\run - line 15

if possible, you need to have your sql server administrators change the production account from the network service account to an actual domain account. The network service account usually has minimal permissions to run in sql server. While great for demo environments, I'd avoid this on a production environment.

Plus, as you can see from this article, if SQL and the Dynamics AX AOS, are on separate machines, that you should not be using the network service.

technet.microsoft.com/.../dd362055.aspx

Instead, I would configure a domain account and use that for the AOS.

Anyway, thankfully, you can fix this by going on the production server and adding the 'showplan' permission to the network service account if you can't change it (and the AOS and SQL Server are on the same machine).

msdn.microsoft.com/.../ms189602(v=sql.105).aspx

Quick Links