Recently, I received an email from a user saying that on one of their SQL Servers, a developer was trying to use Database Engine Tuning Advisor (DTA) and the user was unable to use the tool, as there was an error message being displayed. After few emails back and forth on finding what exactly was the issue/error, I tried to simulate on my local machine and found out the remedy. 

Solution

This cannot be referred as a problem as such, but it needs to addressed before DTA is used. Database Engine Tuning Advisor should be initially connected (and/or used) by a user who is part of sysadmin server role. Once a user (who is a sysadmin) has successfully connected to the SQL instance through DTA, it gets initialized and as part of the initialization, there are a series of tables that are created in MSDB to store data related to further usage of the DTA tool. 

From  then on, users who have db_owner access to a certain database can run workloads against that database and get recommendations to tune their queries/workloads..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty