Create Grant Statement for All Custom Views in Microsoft SQL

Microsoft SQL ServerOn a recent project, I needed to create a new database role for some new SQL views which had been created.

Creating the role is easy enough, but to make sure all the relevant views (and there were quite a few) looked like a bigger job until I realised I could select from sys.views and generate the grant script at the same time.

In the below script, the first highlighted section is the database role and the second the start of the views to have select permissions granted (I always use a uv_ prefix for views):

/*
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 'GRANT SELECT ON ' + name + ' TO urpt_SalesViews' + CHAR(10) + 'GO' FROM sys.views WHERE name LIKE 'uv_[/highlight%'

The output of the above should be returned to text and can then be copied and pasted into a new script window.

What should we write about next?

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

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