I have user having only read only access to database. I want to only grant read only access to few views other read only access to tables, Stored procedure needs to be revoked?
Go to the object properties you want to add permissions to and in Permissions add the user and select Grant Select from the list. So, for Views expand folder structure in SSMS and right click the view - Properties - Permissions.
Otherwise create new query with something like:
use YourDB GRANT SELECT ON OBJECT::[dbo].[myview] TO User1 GO
I am not sure what i am missing when i connect ssms by using the read only user , finding all table, views ,Sp etc visible for the user ?
Just want a user can see the view structure containing selected views visible with read only permissions?
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database, they cant execute SP. Your user adventureworksro has table and view read access to the AdventureWorks DB only. Can they select data from the DB?
IF you don't want the user to have access to all tables and views in the DB don't use the db_datareader role, just grant Select on specific objects.
Thank you worked ...Many thanks.
No probs good!