Nearly a week back, I was facing a weird issue. SDT Advance Options were not working in spite of enabling it.
David Musgrave, as always, came to my rescue and gave me some points to troubleshoot. The following are those points:
1. Is Advance Mode active? YES
2. Have you logged on as SA? NO. I was logged on as DYNSA
3. Does SA has sysadmin rights in SQL? YES it does. But DYNSA did not.
That’s the problem. DYNSA did not have required role(s) on DYNAMICS and Company DBs. It’s always a matter of concern for all SQL Admins to assign this sysadmin role to some user, but it’s not an issue if it’s allocated to DYNSA, since this user is created specifically by Dynamics GP for it’s maintenance.
Once I gave this role to DYNSA, I was able to access Advance Mode in SDT.
Just make sure that, if you specifically use DYNSA for GP SQL Maintenance, then it must have sysadmin role attached to it.