I 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.