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 an IT Professional (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.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.