How to Grant Select rights on all views including future views in SQL Server

Question

How to Grant Select rights on all views including future views in SQL Server

Hazir 11

I have multiple views and want to create more views in SQL Server. I want to grant select rights automatically to some users as I create view. Kindly mention code.

Justin Price 41 Reputation points

2023-03-03T14:56:48.02+00:00

@Cris Zhan-MSFT So, this worked great to grant the permission we needed. However, we put a revoke at the end of the View after the Select and a GO statement, and we get a Select permission not granted error again. Without the revoke line, the data comes through, but the User still has the select permission.

How is it possible to revoke the permission once you have the data you need?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-03-03T15:38:27.5833333+00:00

House rules: don't piggyback on old questions, but ask a new question, describing your problem from start to end.

1 answer

Your answer

Justin Price 41 Reputation points

2023-03-03T14:56:48.02+00:00

@Cris Zhan-MSFT So, this worked great to grant the permission we needed. However, we put a revoke at the end of the View after the Select and a GO statement, and we get a Select permission not granted error again. Without the revoke line, the data comes through, but the User still has the select permission.

How is it possible to revoke the permission once you have the data you need?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-03-03T15:38:27.5833333+00:00

House rules: don't piggyback on old questions, but ask a new question, describing your problem from start to end.

Answer 1

Hi,

If these users currently do not have permission to access the view object.

For the existing view, you can go to the Properties of the view in SSMS, add users in the Permissions, and then grant select permission in the permissions list. Or use the following statement to grant user permissions:

use YourDB
GRANT SELECT ON OBJECT::[schema].[yourview] TO User1,User2

For views to be created in the future, you cannot grant user any permission while creating them. You can choose to grant permissions after creating views.

Or create a new schema for these views, and grant these users the right to select objects in this schema .It is necessary to ensure that the owner of the schema of the original tables and the schema where the view is located are the same.

use YourDB
GRANT select ON Schema :: [DBO] TO User1

Note that you can GRANT SELECT permissions on a Schema, but you are unable to limit the SELECT privileges to views only.

Cris Zhan-MSFT 6,661 Reputation points

2021-06-15T02:31:02.117+00:00

Hi @Hazir ,

we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

Share via

How to Grant Select rights on all views including future views in SQL Server

1 answer

Your answer