Smartlist Builder Security for SQL Server Views

On a recent Microsoft Dynamics GP newsgroup post, a user created a Smartlist Builder based on a SQL Server view. After granting permissions to the DYNGRP database role, the Smartlist was still not accessible to the end user. After testing a few things, fellow MVP Victoria Yudin was able to resolve the problem by assigning a specific Smarlist Builder security task to the user's Dynamics GP role.

Recreating the issue

The following steps are needed to recreate the issue.

1) Run the following statement against your SQL Server company database. This simple view will show all contact information on sales documents that have been purchased from suppliers.

CREATE VIEW dbo.vwPurchasedSOPDocuments AS
SELECT a.sopnumbe, a.docdate, a.custnmbr, a.custname, a.cntcprsn, c.ponumber, c.vendorid, c.vendname, c.docdate as podate, c.contact
FROM SOP10100 a INNER JOIN SOP60100 b on (a.SOPNUMBE = b.SOPNUMBE) and (a.SOPTYPE = b.SOPTYPE)
INNER JOIN POP10100 c on b.PONUMBER = c.PONUMBER
GO

GRANT SELECT ON dbo.vwPurchasedSOPDocuments TO DYNGRP
GO

2) Login to GP as SQL Server's system administrative user (sa) and open Smartlist Builder SQL Table Security window and grant access to the created object. Choose the Views radio button to display the recently created view. Click OK to continue.


















3) Setup a new Smartlist object as follows:

















NOTE: Use the SOPNUMBE and PONUMBER fields as keys to the SmartList.

4) Save and open Smartlist to build the newly created object.











5) For this demonstration, grant access to the newly created Smartlist by assigning security to the DEFAULTUSER security task. Go to Microsoft Dynamics GP > Tools > Setup > System > Security Tasks.


















6)Switch users. Select a user with DEFAULTUSER security task and log on as that user into GP. For this sample, I will be using LESSONUSER1 in Fabrikam.

7) Open Smartlist and try to launch the Smarlist previously created. The user (LESSONUSER1) will obtain the following error:
















While all the above procedures to setup security priviledges to the newly created Smartlist are standard and would seem accurate, there is one more step that was omitted throughout. For an end user to have access to Smartlist Builder objects created from views, it is necessary to grant one more access to the corresponding security task, as follows:

1) Log into Dynamics GP as SQL Server systems administrator (sa).

2) Open the Security Task Setup window and choose the task ID. In the case of this example I selected DEFAULTUSER. Under Product, choose SmartList Builder; under Type, choose SmartList Builder Permissions; and under Series, choose SmartList Builder. Mark View SmartLists with SQL Tables in the Access List pane. Click Save to continue.


















3) Log on as the user in question (LESSONUSER1), then attempt to open to launch the newly created SmartList. At this stage, the security issue should have been resolved and the user is now able to view the Smartlist.

Good catch Victoria!

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/