SharePoint Error 6398 - You do not have permission to run 'SP_TRACE_CREATE'
Working with a customer, we noticed a large number of errors in the Windows Application event log for error 6398. The errors look like:
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPSqlDeadlockDiagnosticProvider (ID c020fbe2-7771-495c-95dd-ad7fb680e2e5) threw an exception. More information is included below.
You do not have permission to run 'SP_TRACE_CREATE'.
and
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPIOIntensiveQueryDiagnosticProvider (ID 85c74911-f645-4c61-977b-81914d9eb69b) threw an exception. More information is included below.
You do not have permission to run 'SP_TRACE_CREATE'.
SharePoint 2010 introduced the Health and Usage Data Collection component. One of the (if not the most) definitive sources for understanding this capability is by reading the blog series that Todd Carter did on Extending the SharePoint 2010 Health & Usage. The errors above mention a diagnostic provider that is attempting to perform an unauthorized action.
Start by looking at the diagnostic providers in your farm. When you install the SharePoint 2010 Administration Toolkit, it includes a fantastic component called SharePoint Diagnostic Studio (SPDIAG). This component is able to query the usage data store and provide reports that help you manage the health and performance of your SharePoint 2010 environment. When you create a new project, it will install the diagnostic providers and start collecting health data.
SPDIAG will install several diagnostic providers that collect additional data. You can view these in the Central Administration web UI, but PowerShell makes this task simple using Get-SPDiagnosticsProvider.
PS C:\Windows\system32> Get-SPDiagnosticsProvider
Name Retention MaxTotalSi Enabled
zeInBytes
---- --------- ---------- -------
job-diagnostics-blocking-query-provider 14 6200000000 False
job-diagnostics-sql-dmv-provider 14 1000000... True
job-diagnostics-uls-provider 14 1000000... True
job-diagnostics-performance-counter-sql-provider 14 6200000000 True
job-diagnostics-performance-counter-wfe-provider 14 1000000... True
job-diagnostics-event-log-provider 14 6200000000 True
job-diagnostics-changed-objects-provider 14 6200000000 True
job-diagnostics-sql-blocking-report-provider 14 6200000000 True
Search Health Monitoring - Trace Events 14 1290000... True
job-diagnostics-io-intensive-query-provider 14 1000000... True
job-diagnostics-change-types-provider 14 6200000000 True
job-diagnostics-sql-memory-provider 14 1000000... False
job-diagnostics-sprequestusage-provider 14 6200000000 True
job-diagnostics-sql-deadlock-provider 14 6200000000 True
Compare this to the results of an out-of-box installation without SPDIAG, and you can see that SPDIAG adds several diagnostic providers. The ones that are throwing the 6398 are the three jobs that are trying to collect information about SQL.
- job-diagnostics-sql-deadlock-provider
- job-diagnostics-sql-blocking-report-provider
- job-diagnostics-io-intensive-query-provider
These diagnostics providers use SP_TRACE_CREATE and RECONFIGURE to gather data needed to support several reports in SPDIAG.
From https://technet.microsoft.com/en-us/library/hh144782.aspx:
Some SPDiag diagnostic timer jobs require sysadmin or sqladmin privileges
Some SPDiag 3.0 diagnostics jobs require that the farm account has the sysadmin or sqladmin role assigned on the SQL Server instance where the SharePoint 2010 Products databases are located. If the farm account does not have these roles assigned, it will have insufficient privileges to run diagnostic jobs that are required for certain reports to gather data.
SP_TRACE_CREATE and RECONFIGURE are available to the sysadmin and serveradmin fixed server roles implicitly. These permissions are not available to the db_owner database role. The account that is trying to create the trace is the SharePoint farm account. By default, this account has db_owner database permissions, but it does not have server permissions and is not included in a fixed server role. That’s why you see the error above.
At this point, you have a few choices:
- If you find value in the blocking, IO, and deadlock reports for proactively managing your environment, then add the farm account to the fixed sysadmin or sqladmin role.
- If you want to use SPDIAG but do not want to grant that level of permission to the SharePoint farm account, you can still use it without those diagnostic providers, you just won’t get the database specific reports (blocking, IO, deadlocks).
To disable these providers, you can run the following PowerShell script. Remove the line breaks in the IF statement below.
$providers = Get-SPDiagnosticsProvider
foreach($provider in $providers)
{
if(($provider.Name -eq "job-diagnostics-sql-blocking-report-provider")
-or ($provider.Name -eq "job-diagnostics-io-intensive-query-provider")
-or ($provider.Name -eq "job-diagnostics-sql-deadlock-provider"))
{
$provider.IsDisabled = $true;
$provider.Update();
}
}
We run the Get-SPDiagnosticsProvider PowerShell script again to confirm our changes.
PS C:\Windows\system32> Get-SPDiagnosticsProvider
Name Retention MaxTotalSi Enabled
zeInBytes
---- --------- ---------- -------
job-diagnostics-blocking-query-provider 14 6200000000 False
job-diagnostics-sql-dmv-provider 14 1000000... True
job-diagnostics-uls-provider 14 1000000... True
job-diagnostics-performance-counter-sql-provider 14 6200000000 True
job-diagnostics-performance-counter-wfe-provider 14 1000000... True
job-diagnostics-event-log-provider 14 6200000000 True
job-diagnostics-changed-objects-provider 14 6200000000 True
job-diagnostics-sql-blocking-report-provider 14 6200000000 False
Search Health Monitoring - Trace Events 14 1290000... True
job-diagnostics-io-intensive-query-provider 14 1000000... False
job-diagnostics-change-types-provider 14 6200000000 True
job-diagnostics-sql-memory-provider 14 1000000... False
job-diagnostics-sprequestusage-provider 14 6200000000 True
job-diagnostics-sql-deadlock-provider 14 6200000000 False
Note that there have been other cases where error 6398 is thrown because of SQL aliases (see the For More Information section below).
For More Information
SharePoint 2010 Administration Toolkit
SharePoint 2010 Diagnostics Studio
Extending the SharePoint 2010 Health & Usage
Timer Event 6398 – SPDatabaseServerDiagnosticsPerformanceCounter
SQL Aliases and SP_TRACE_CREATE errors on SharePoint 2010
Comments
Anonymous
February 15, 2015
Great article! Thankyou for sharing!Anonymous
January 11, 2016
These permissions seem excessive to me!As this article suggests:https://sharepointsoldiers.wordpress.com/2011/11/12/eventid-6398-you-do-not-have-permission-to-run-sp_trace_create/Alter Trace” and “Alter Settings” should make the trick.