Creating a new SmartList from a SQL View

In the Creating a new SmartList recipe, we created a new SmartList object using GP tables. SmartList Designer also allows SmartLists to be created from SQL Views, which allows for more flexibility in how SmartList Designers work.

One example of this, and one used by many of my clients, are SmartLists that report on the security configuration; these were created as SQL Views to make them easy to transfer between systems.

Getting ready

Before the SmartList can be created, the SQL View needs to be loaded into the relevant database; for security data, this is the system database (typically called DYNAMICS), but for many SmartLists, the SQL View will need to be loaded into all company databases.

The security-related SQL View (uv_AZRCRV_UserAccessAndGrantedSecurityViews) that we will use in this recipe is the User Access and Granted Security Roles, http://azrcrv.co.uk/y7xXlZ

Download the view and load it into SQL Server in the system database, making sure...