locked
User permissions to run Tuning Advisor RRS feed

  • Question

  • Hi,

    One of my Application user is asking me to give permissions to run SQL tuning advisor.

    I read some articles saying that only DB_OWNER can run the tuning advisor, but i can not give the DB_OWNER permissions to an application user.

    I am looking for an explicit role to run the tuning advisor or any other database level role can run the tuning advisor.

    Please help me on this.

    Thanks,


    Vinodh Selvaraj

    Tuesday, July 7, 2015 11:14 AM

Answers


  • I am looking for an explicit role to run the tuning advisor or any other database level role can run the tuning advisor.

    db_owner is the database role you need. more info: 

    After you install SQL Server, a user who is a member of the sysadmin fixed server role must launch Database Engine Tuning Advisor before anyone else. When the sysadminuser launches Database Engine Tuning Advisor that action initializes the application for use by others. After Database Engine Tuning Advisor is initialized, any user who is a member of the db_owner fixed database role can use it to tune tables on databases they own. 

    from Permissions Required to Run Database Engine Tuning Advisor


    Saeid Hasani (My Writings on TechNet Wiki ,T-SQL Blog)

    Tuesday, July 7, 2015 1:08 PM

All replies

  • Consider the safety of your data before granting other users dbo permissions.

    You could create a temporary SQL Account (temp_user?); give SQL Login dbo permissions to the database and then advise the user of the credentials and also advise the user they have 2 days to use the Tuning Advisor before you delete the account.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, July 7, 2015 11:26 AM

  • I am looking for an explicit role to run the tuning advisor or any other database level role can run the tuning advisor.

    db_owner is the database role you need. more info: 

    After you install SQL Server, a user who is a member of the sysadmin fixed server role must launch Database Engine Tuning Advisor before anyone else. When the sysadminuser launches Database Engine Tuning Advisor that action initializes the application for use by others. After Database Engine Tuning Advisor is initialized, any user who is a member of the db_owner fixed database role can use it to tune tables on databases they own. 

    from Permissions Required to Run Database Engine Tuning Advisor


    Saeid Hasani (My Writings on TechNet Wiki ,T-SQL Blog)

    Tuesday, July 7, 2015 1:08 PM
  • And why would an application user need to run DTA? How would a user have detailed knowledge of the database schema and the queries associated with any particular process? 
    Tuesday, July 7, 2015 1:45 PM
  • And why would an application user need to run DTA? How would a user have detailed knowledge of the database schema and the queries associated with any particular process? 

    Excellent question.

    Why indeed?  The Database Tuning Advisor is something for a DBA or Developer to use; why and end user?

    The only other thing I can suggest is that if the user has a valid reason then they perform the task they need at your desk with your login, with you supervising.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, July 7, 2015 3:54 PM