Hi,
I want to grant a user to access only one view in my database and disable the rest.
Is there a way to do this in SQL 2005.
Thanks in advance
Pegeot2000
Printable View
Hi,
I want to grant a user to access only one view in my database and disable the rest.
Is there a way to do this in SQL 2005.
Thanks in advance
Pegeot2000
Sure, when you create the user in a database do not grant any default role. Then run
grant select on yourview to theuser
When a user is created, the public role is assigned automatically and the public user has select permission which gives the user full access to view any table or view.
No, public role doesn't have select permission by default.
But the problem is when I just create a user, it can view all the tables and views in the primary database.
Can see table names or can query from tables?
Yes, both options are possible with a new user, who’s not given any permission except for the public role which is assigned automatically at the time of creating it.
You have to double check public role's permission in the db. It doesn't have permission on user tables by default.
Could you kindly tell me how to check on the Public role’s permission in SQL 2005 and how I could change them according to my needs.
Open public role's properties in ssms and look at securables.