Index Tuning Wizard Permissions

[mkelly11]

I have a developer who can't run the Index Tuning Wizard even though he is listed as dbo. He get's an error message stating "to perform this task you must be a member of sysadmin". Can anyone tell me if there is a way around this, my manager doesn't want anyone other than myself (the DBA) to have sysadmin rights. Help!!

 

[ClaireHCM]

Does your developer has MSDE in his workstation. He could use DTS package to copy the whole database(if the size of the database is not too big) to his personal workstation. Definitely he will be a sysadmin in his own workstation, then run the index tunning wizard for analysis.

 

[mkelly11]

Yes, he does have MSDE on his workstation. Now since I'm a newbie to the DBA world, is this an accepted practice in med to large companies? I'm entirely self taught and do not have any "Mentors" at my company. In your experience have you found it to be acceptable for developers to have "admin" rights on a sandbox server? Are there any severe risks to giving developers admin rights on server that's not in a production environment? The reason I ask is that we have a relatively small development team, where several of us may need to work on the same project. Having developers working from individual installations of MSDE does not lend itself to be a collaborative work environment. Shortly I will be demoting one of our servers from production to development and I'm a little apprehensive to give too much leverage to our developers. Any suggestions would be greatly appreciated.

 

[ClaireHCM]

For database developer, you might give them admin authority in Dev region, sometimes in QA region, but not in production environment.Reason behind this is easy for developing and all the codes apply in a sub-real situation. In MSDE you will never run into network connection, Server hardware issues and some unexpectable problem when u run in one person world msde.And database developer do possess certain level of knowledge how to manage the database for better performance.

For application developer, it's better to grant execution permission on stored procedures, sometimes data reader and data writer, but no sysadmin,NO!

 

[ClaireHCM]

One more thing is, Index tuning wizard is usually used for index analyzing, but rarely we follow whatever index tuning wizard says, it provides the recommendation for indexes that might be required to be created but not certainly all the time. And it also depends on which trace file you provide for index tuning wizard to analyze. You may want to catch up multiple trace files or long-running trace in order to provide more accurate index creation analysis. Sometimes by copying the whole database, you could see from EM, some indexes SQL server already creates for you because it thinks it might helps you for better performance, in this case you could also get some index information that you were missing but sql server catches for you. But as I said, SQL Server doesn’t always make the right decision, you may need to analyze from different perspectives and get the better idea which index needed to be created.