patternsqlMinor
Is there a way to grant a user access to only all non system views of database?
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
You can
to generate a script to run to
GRANT SELECT ON ALL::Views TO SomeUserYou 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 = 0to generate a script to run to
GRANT the required permissions.Code Snippets
GRANT SELECT ON ALL::Views TO SomeUserSELECT 'GRANT SELECT ON ' +
QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) +
' TO ViewReader;'
FROM sys.views
WHERE is_ms_shipped = 0Context
StackExchange Database Administrators Q#15276, answer score: 8
Revisions (0)
No revisions yet.