HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is there a way to grant a user access to only all non system views of database?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
allnonusersystemgrantwayviewsdatabasethereonly

Problem

In SQL Server, I have a user in a particular database and I've been asked to grant them access to all of the non-system views of the database only. I believe this can be done by editing securables of type view and granting select on each one, but there are many, many views. Is there a more efficient way to accomplish this?

Solution

There is no syntax such as

GRANT SELECT ON ALL::Views TO SomeUser


You can GRANT SELECT permissions on individual objects, schemas or the entire database but not filtered by object type to only include Views. For this sort of adhoc task I'd probably create a new role called ViewReader, add the user to that role then in SSMS run

SELECT 'GRANT SELECT ON ' + 
          QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + 
      ' TO ViewReader;'
FROM sys.views
WHERE is_ms_shipped = 0


to generate a script to run to GRANT the required permissions.

Code Snippets

GRANT SELECT ON ALL::Views TO SomeUser
SELECT 'GRANT SELECT ON ' + 
          QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + 
      ' TO ViewReader;'
FROM sys.views
WHERE is_ms_shipped = 0

Context

StackExchange Database Administrators Q#15276, answer score: 8

Revisions (0)

No revisions yet.