CODEWITHSUNDEEP
sqlsql-serverazure
rocky_pps
rocky_pps

Reputation: 153

How Grant Select access to view in schema

Need to Grant an access to only the view of XYZ schema, View internally call the Table from dbo Schema. need to restrict access to dbo table. as i am only giving access to XYZ schema when i try to slect XYZ.VIEW. it gives and error:

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Table1', database 'servername', schema 'dbo'.

Query to Grant access to user

GRANT SELECT ON XYZ.VIEW TO user1

XYZ.VIEW Definition is below 

SELECT NAME, Adress
FROM  dbo.Table1

Upvotes: 1

Views: 309

Answers (1)

iosappdevguy
iosappdevguy

Reputation: 55

If you don't want the user to access the table and all of its contents and only access what you have in view, then create a Materialized view. This will isolate the user to what you give them and will remove the need to give them access to the table.

Upvotes: 1

Related Questions