Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
Thank you for the extremely graceful answer. The script is a little over my head so I'll do some reading before trying to implement. Thanks again. –  lush Mar 19 '12 at 22:24
1  
@lush - The script just gives you some commands to review, copy, paste and run. You could do something more elaborate to concatenate the result and EXEC but I wouldn't bother for a one off task. –  Martin Smith Mar 19 '12 at 22:27
    
I understand now, this is a perfect solution for this problem, thanks! –  lush Mar 19 '12 at 22:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.