Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
erictatmanumit
Starting Member
2 Posts |
 Posted - 2010-05-20 : 10:55:14
|
| I have a system I am trying to secure. The users access it using msquery. I have set up a new role and added all of the database objects to the role and then given access to the views I want them to see. However - msquery allows the user to list objects for all users - this gives the sys and INFORMATION_SCHEMA objects.Is it safe for me to add the user group to the master database and then deny select on these - or is this going to stop the users functioning?Any suggestions - in reality I just want to grant specific users specific views and nothing else - there has to be a better way! |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-05-20 : 20:43:58
|
| in reality I just want to grant specific users specific views and nothing else- create user(s) with login is public role, nothing else.- create view(s) you want user(s) to see the data.- create a role, then add the user(s) in this role, then in Securables, add the view(s) in this role. On permission, check Grant in Select.You are done!When the user(s) login, they only can get data via the view(s) you create for them, but nothing else. |
 |
|
|
erictatmanumit
Starting Member
2 Posts |
Posted - 2010-05-21 : 02:54:52
|
| So I thought, but - The public views of sys and INFORMATION_SCHEMA also appear in the MSQUERY drop down - The only way to get rid of them appears to be to deny select on them for the user (group) This is tedious and creates a big maintenance job (These can only be hidden by changing the permissions in the master database) |
|
|
|
|
|
|
public role