October 15, 2013 at 9:04 am
I would like to have a credential limited to read only access to the tables in a database.
I have a created such a user with a default schema of 'mydbschema' and a Database role membership of 'db_dataeader'
When connecting to the database with SSMS as that user I only see one table. This table has a schema of 'dbo.' The other tables (that the user needs to see) has a schema of 'mydbschema.'
What do I need to do so that the user has read only access to the tables with a schema of 'mydbschema?'
October 15, 2013 at 9:14 am
Making your user a member of db_datareader will grant the user read access to the entire database.
I think you want to remove any role memberships and grant select access just to the schema:
USE [databasename];
GRANT SELECT ON SCHEMA::mydbschema TO [UserName];
October 15, 2013 at 9:16 am
Exactly what I was going to say. I would also have added that this will also grant SELECT on views in the schema, so be careful if that isn't your intention.
John
October 15, 2013 at 12:21 pm
sestell1 (10/15/2013)
Making your user a member of db_datareader will grant the user read access to the entire database.
That's what I thought would happen, but it didn't. The user account only saw the tables in the 'dbo' schema, but did not see any tables in the 'mydbschema' schema.
But, after running the GRANT SELECT statement you provided, the user now has read access to the tables in question.
Thank you very much for your help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply