Select all user views and create GRANT SELECT TO DYNGRP statement

Microsoft Dynamics GPI have a Microsoft Dynamics GP project deployment coming up soon for a client project for which a number of SQL views have been created; I can easily grab a create script for the views from Object Explorer Details in SQL Server Management Studio, but I also need to create the grant statement to give permissions to Dynamic GP users to use the SmartLists Builder objects which use these views.

INFORMATION_SCHEMA.VIEWS is available for querying in SQL Server and allows you to get a listing of the views. The following script selects all views with my custom user view prefix (highlighted).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT CONCAT('GRANT SELECT ON ', TABLE_NAME,' TO DYNGRP', CHAR(13), 'GO') FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'uv_AZRCRV_%'

Run the script with the output set to text as for each view the script creates the GRANT and subsequent GO commands.

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you’d like to speak to someone about consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?

Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *