August 6, 2010 at 1:00 pm
Hi,
I am not sure if this possible, but I would like to limit certain users visibility to Views only. I do not want them to be able view tables and stores procedures but only be able to view Views with select statements.
I am newbie so please bear with me.
What I am doing is creating a database in SQL Server 2008 express edition and allow users to query report using Report Builder 3.0. But when I connect to database while creating a new dataset, I see all the tables and stored procedure along with Views, which contain SELECT Statements. I want user to be able to only see Views so they can only run queries in Views.
So, my question is; is it possible to limit users viewing only Views? is it possible to not allow them to view tables and Stored procedures?
Thank you in advance.
August 6, 2010 at 2:41 pm
The users you created, what rights have you given? Give the User just Public right to the DB. Create a DB Role. Give rights for Select to just the views to the role you just created. Add the user that is usibg the views to that role.
-Roy
August 6, 2010 at 3:26 pm
To restrict the access for database objects(table, SP , View etc) best approach is the create the Database role with minimum permissions you wants for objects and then add your user to this database role ...
HTH ...
August 6, 2010 at 4:43 pm
Thank you Roy and nary for your answers.
I tried creating a new database role and added users to that role and now only thing they have visibility to is Views with Select execute only permissions.
August 6, 2010 at 10:30 pm
Roy,nary
HERE IS THE PROBLEM!!!
This kind of user would be able to see your all the system views though. Is there anyway to bar him from seeing system views as well.
(I created the db role 'dbrole2' and gave him viewdefinition grant permission only on one particular view. Now I created one user and attach him with 'dbrole2' as databaserole membership )
Is there any solution for this?
-Lucky
August 8, 2010 at 9:52 am
Sure.
I recommend making a database role and selecting the individual views you want these users to be able to see. Then map these uses to that newly created database role.
If you're using SQL Server Management Studio, go to the database, select security->roles->Database Roles. Right click to create a new role. Go to the securables page. Click search, select all objects of the type, view. From there you can click on each view you want them to see, and check the "Select" permission.
August 8, 2010 at 2:20 pm
Roy, nary and tnk,
My question remains the same. New user with this database role would be able to see through my dmvs... Could you read my question once again...
-Lucky
August 8, 2010 at 6:17 pm
Hi luckysql.kinda
I am not sure how you have done permissions, but I created DB Role as Roy and nary suggested and same way tnk described. When I created role and added permission for object Views, I was able to select Views. So, for the DB Role I created; I was able to only give user access to the view I wanted them to have access to and avoid giving them access to System Views.
But in my situation, I was able to avoid giving access to system views and system procedures to users.
Viral
August 8, 2010 at 11:15 pm
Lucky
I suspect it is because you have granted the "View Definition" permission to your role rather than the "Select" permission.
"View Definition" allows the role members to see the T-SQL underneath the views and does not actually allow the role members to select from the views.
Change your permissions to "Select" and remove "View Definition" and try that.
Martin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply