Hiding tables from users

• Skanker

  Hall of Fame

  Points: 3059

  More actions

  February 24, 2011 at 5:06 am

  #247255

  I am very new to SQL and would like some help on hiding tables from specified users.

  I have a SQL 2005 database which is used primarily as a reporting database to provide data for reports published in reporting services.

  I have a request from some users to have access to specific tables within the database on a read only basis.

  I have managed to set this up by creating a specific schema for the table that they which to access. This works ok, however from my point of view I would like to be able to hide all other tables from these particular users.

  Any help would be greatly appreciated.

  The Database is call 'CAYA_Service_Reporting'. The schema I have set up is 'Data_Analysis'. I have added users with a prefix 'DAT' then their ID.

  Thanks

  Tim

• John.Liu

  SSCommitted

  Points: 1606

  More actions

  February 24, 2011 at 5:28 am

  #1290889

  One way might be set up a database role and grant "deny access" to those tables you want to hide from the user. Add those users as that role member.

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  More actions

  February 24, 2011 at 5:28 am

  #1290888

  Only grant them access to the schema with the table in it - nothing else. At DB level they only need to be a member of the public role and have access to the schema.

  Careful not to give them membership to db_datareader fixed database role, this will give read to all tables.

  Have a muck around with a SQL user (provided SQL is in mixed mode).

  Carlton.