Securing a schema with db_datareader

• doronyaary

  SSC Veteran

  Points: 224

  More actions

  January 18, 2011 at 7:19 pm

  #232329

  Hello to all SQL Server DBA`s arround the world !

  I have a bit of a problem and I will greatly appriciate any help. I have a user called "remote_reader_2" which has a mapping to a default schema called "Sales" on the old a good AdventureWorks database.

  Now, I have assigned my user with the role of db_datareader because I want him to only run SELECT commands against the data. How ever, the user can actually see all of the schemas in my database.

  How can I use the db_datareader against only one schema ??

  Thanks !

  Doron.

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  January 19, 2011 at 7:34 am

  #1275307

  Try granting SELECT on the schema instead of using the db_datareader role.

  DROP USER remote_reader_2

  go

  CREATE USER remote_reader_2 FOR LOGIN remote_reader_2 WITH DEFAULT_SCHEMA = Sales

  go

  GRANT SELECT ON SCHEMA::Sales TO remote_reader_2

  go

  _____________________________________________________________________
  - Nate

  @nate_hughes