On further investigation i found that she was trying to run Database Engine Tuning Advisor. And she was getting the error [Microsoft][SQL Server Native Client 10.0][SQL Server]SHOWPLAN permission denied in database ‘database’. When she was trying to execute the trace load.
Initially i thought that’s easy will provide rights to the group using
grant showplan to [domainuser]
and job done. But this was not the case. To add to the confusion she was ( rightly ) tracing the statements using the application user. The application user again is not part of sysadmin.
There are things we need to take into consideration. When you catch the sql statements you want to tune using profiler you need to execute these statements using the sql / domain user who is a sysadmin user.
Also, on further reading through MSDN site i found that the very first time the Database Tuning Adviser needs to be run by user with Sysadmin permission or they should tune the statement first. After that all the other users who have SHOWPLAN permission will be able to use the DTA.
So the solution to my problem was now clear. I ran the DTA graphical interface with simple load of select * from … and provided the showplan rights to the application user. Since this was test environment it was easy enough for me to provide the rights to the user.