Post reply

Access to Database table

  • September 27, 2011 at 5:38 am

    #390481

    Would like some help in providing read access to only one table in a database. Is there a script that can be run to provide that access. I have tried doing it via the gui by giving the user the db_reader role but the user can still see and read other tables in the database. We need the user to only be able to read and view one table in the database.

    Please pardon my lack of knowledge. I am fairly new to this.

    Appreciate your help very much.

    Reynold

  • September 27, 2011 at 6:07 am

    #1387063

    Hi,

    You can add the user to the database with no roles assigned;

    Then go to Database >> Security >> Users; right click on the user >> properties; select securables>> click search.

    Under search you can specify either stored procedures, views, tables etc. and give him granular access on only specific objects.

    This is the process for Management Studio 2008+ to do it through the GUI.

    Regards...

  • September 27, 2011 at 6:27 am

    #1387084

    In SSMS:

    Create the user with no extra roles.

    As admin, right click on the User -> User Mapping.

    Tick the database you want to map their username to, make sure only public is ticked as role.

    Then run the following statement:

    use <dbName>

    grant select on object::<schema>.<tableName> to <userName>

    Just tested it on my machine and I could only see that 1 table logged in as that user.

    http://sqlvince.blogspot.com/[/url]

  • September 27, 2011 at 6:56 am

    #1387110

    Wonderful. Thanks so much. I will go and try and I am confident that will work.

  • September 27, 2011 at 2:15 pm

    #1387444

    rjoseph (9/27/2011)

    Wonderful. Thanks so much. I will go and try and I am confident that will work.

    The db_datareader role is a fixed database role that grants read access to all tables and views in a database.

    Access to a specific table as required in this case can be done via ssms as suggested.

  • September 28, 2011 at 4:25 am

    #1387645

    It works wonderfully. Once again, thanks for the script and steps. That was what I really needed.

  • September 28, 2011 at 4:37 am

    #1387653

    It's a pleasure, that's why we are here 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply