February 24, 2011 at 5:06 am
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
February 24, 2011 at 5:28 am
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.
February 24, 2011 at 5:28 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply