Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?]

share|improve this question

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.

share|improve this answer
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 '09 at 16:51
Column permissions is too specific, and won't be enabled until you select an object that has columns. – MartW Sep 30 '09 at 19:57

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.