0

Newbie question: I have a designer who reads information from a SQL Server [2008] database. I've setup a few views for him to pull his data from and granted his user a member of the db_dataread role. His program uses an ODBC connection to grab the data.

However, this allows him read access to all of the tables and system views and tables, requiring unnecessary searching and confusion for the user.

Is there a way to restrict what he can see to only the handful of views I've created for him?

[EDIT: I've created a new test user, and a new test ODBC connection, giving no permissions at all with no change in results. From what I've read, it is because all users are part of the PUBLIC server role. The public role appears to give SELECT privilege to all system objects. Anyone know otherwise?]

2 Answers 2

3

Remove the user from the db_datareader role and just give it SELECT rights against the views you created for it. You don't mention which version of SQL Server, but the start point is invariably opening the user's properties screen and looking for something that says Permissions.

2
  • I'm using 2008. Under Securables in the User Properties, there is a Permissions area with a disabled "Column Permissions" button. Same if creating a role. Trying to figure that out.
    – Feckmore
    Sep 25, 2009 at 16:51
  • Column permissions is too specific, and won't be enabled until you select an object that has columns.
    – MartW
    Sep 30, 2009 at 19:57
1

CodeByMoonlight is correct. You need to remove the db_datareader role.

You have a couple of different options here:

  1. Do as CodeByMoonlight suggests and grant the user select permissions only on the views that you want him to have. This is great if you never plan on having any other user that will need acess to the same type thing.

  2. Create a Schema and assign it the permissions CodeByMoonlight suggest. This would allow you to give the same access to another person.

    2008 Schema Permissions Chain = Server.DB.Schema.Object

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .