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?

2 Answers 2


There is no syntax such as


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

          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.

  • 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. Mar 19, 2012 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. Mar 19, 2012 at 22:27
  • I understand now, this is a perfect solution for this problem, thanks! Mar 19, 2012 at 22:42

I took another approach in my case. This is what I did:

  1. Create a login and map it to a database.
  2. Go to a database an create a schema called Public_View for example. The owner of this schema must be the same owner of the tables that the views are gonna refer.
  3. Grant the new user access to the new schema.
  4. Create as many views as you want in the new schema and the new user will have access to them.

The good thing is that the new user will not have access to the tables, he won't even be able to see the tables.

I hope it helps.

  • 4
    +1 To me the good thing about this solution over mine is that there is no need to assign permissions explicitly when new views are created. Aug 6, 2014 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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