When logged into Microsoft Dynamics GP as the ‘sa’ user, the Views item is visible in the Extender main window and will allow me to create SQL views of Extender and GP data.
However, when logged into Microsoft Dynamics GP as other Power Users, the Views menu isn’t visible, even though, as a Power User, I have full rights to every window in GP. Why isn’t the Views menu available for these users?
If you’d rather watch a video demonstration of this, see below or click here.
According to the Extender documentation, the Extender Views menu item will be hidden normally and only displayed when the Microsoft Dynamics GP user has any of the following options:
- User has the ‘sysadmin’ role in SQL.
- User is the Database Owner of the current company (which should be set to DYNSA and not any other user).
- User has the database role of ‘db_owner’ for the current GP company database (Extender 2016 and newer)
In the following screenshot, on the left we see the logged in user is ‘sa’ which is in the sysadmin role in SQL – the Views menu does show.
On the right side, the user is ‘ptr2023’ which is a Microsoft Dynamics GP Power User (or just does have access to the Extender), but the user is an ordinary SQL User with just DYNGRP permissions, and so Extender hides that View menu.
Why isn’t it visible? When you create an Extender View, it will create a SQL View in the company database and grant permissions to the DYNGRP. A regular Microsoft Dynamics GP user doesn’t have permissions in SQL to do this. So, to avoid SQL errors when saving a view, the Views option is hidden if proper permissions are not granted.
In SQL Server Management Studio, we can add the db_owner role for the test user ptr2023 in the TWO23 database that was tested previously.
Closing Extender main window and re-opening it again – this time the Views menu does show as expected, and the Extender Views window will open fine from here.
If you are using Microsoft Dynamics GP 2015 or earlier, that does not support the check for db_owner role or if you have a custom role that should grant the necessary SQL permissions to allow SQL View creation, there is another method to try.
On the navigation pane, right-click and choose ‘Add | Add Window’ and then the window path of:
Extender | Project | Extender Views
The user can click the new Extender Views window, and it will open (provided Microsoft Dynamics GP permissions which PowerUser would have).
This does not grant any sort of SQL Permissions, and so if the user doesn’t have appropriate permissions in SQL, creating the new view will fail. You can see the failure here (after removing the db_owner role).
