Database Engine Tuning Advisor problem

microsoft.public.sqlserver.tools

13 Jun 2006 8:33 PM

MIS

I am trying to the the Database Engine Tuning Advisor in SQL 2005. I start by
running a Tuning trace in SQL profiler and saving it to a file. Next I load
the trace file into the Advisor for analysis. I get the following error in
the log of the advisor: "[Microsoft][SQL Native Client][SQL Server]SHOWPLAN
permission denied in database"

Both tools are run by a user with sysadmin permissions. The items being
traced are executed by a normal user within the public role. My question is,
is it the normal user that needs the SHOWPLAN permission? If not what am I
missing? I am hesitant to give this user these credentials. Although I guess
I could grant them for the duration of the trace.

Any help would be appreciated.

- Dave

13 Jun 2006 11:25 PM

sanjayag

MIS wrote:
Show quote

> I am trying to the the Database Engine Tuning Advisor in SQL 2005. I start by
> running a Tuning trace in SQL profiler and saving it to a file. Next I load
> the trace file into the Advisor for analysis. I get the following error in
> the log of the advisor: "[Microsoft][SQL Native Client][SQL Server]SHOWPLAN
> permission denied in database"
>
> Both tools are run by a user with sysadmin permissions. The items being
> traced are executed by a normal user within the public role. My question is,
> is it the normal user that needs the SHOWPLAN permission? If not what am I
> missing? I am hesitant to give this user these credentials. Although I guess
> I could grant them for the duration of the trace.
>
> Any help would be appreciated.
>
> - Dave

DTA uses the login information from the trace to impersonate when
tuning events.

The following link from books on line describes the possible actions.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dcc4a91d-fd58-4775-ad38-940f9c0bbef5.htm

Thanks

Sanjay

I have pasted the relevant information below.

Trace File or Trace Table Workloads that Contain the LoginName Data
Column
Database Engine Tuning Advisor submits Showplan requests as part of the
tuning process. When a trace table or file that contains the LoginName
data column is consumed as a workload, Database Engine Tuning Advisor
impersonates the user specified in LoginName. If this user has not been
granted the SHOWPLAN permission, which enables the user to execute and
produce Showplans for the statements contained in the trace, Database
Engine Tuning Advisor will not tune those statements. For more
information about the LoginName data column, see Describing Events by
Using Data Columns. For more information about the SHOWPLAN permission,
see Showplan Security.

To avoid granting the SHOWPLAN permission to each user specified in the
LoginName column of the trace
Tune the trace file or table workload. For more information, see How
to: Tune a Database.

Check the tuning log for statements that were not tuned due to
inadequate permissions. For more information, see About the Tuning Log
and How to: View Tuning Output.

Create a new workload by deleting the LoginName column from the events
that were not tuned, and then save only the untuned events in a new
trace file or table. For more information about deleting data columns
from a trace, see How to: Specify Events and Data Columns for a Trace
File (SQL Server Profiler) or How to: Modify an Existing Trace
(Transact-SQL).

Resubmit the new workload without the LoginName column to Database
Engine Tuning Advisor.

Database Engine Tuning Advisor will tune the new workload because login
information is not specified in the trace. If the LoginName does not
exist for a statement, Database Engine Tuning Advisor tunes that
statement by impersonating the user who started the tuning session (a
member of either the sysadmin fixed server role or the db_owner fixed
database role).