question

Hazir-3393 avatar image
0 Votes"
Hazir-3393 asked Criszhan-msft commented

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

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.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Hazir-3393,

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!

0 Votes 0 ·