Post reply

Add a read only user to db

  • April 16, 2008 at 10:35 am

    #124868

    I am a noob to SQL server. I have read many posts but they assume a level of knowledge I don't yet have. What I would like to set up is a user id in my SQL server that has read access to only one database on the server.

    The purpose of the user will be for MSACCESS users to connect via ODBC to the database and see/run queries against any tables but prevent them from making any modifications.

    So far I have created a user called PACREAD on the server who has a server role of public. Under User Mapping, the database "JP_MIRROR" is ticked and no others, the default schema for the user is db_datareader. On the database permissions I have tried to grant on select and connect, then explicitly denied Delete, insert, execute. However when I connect using PACREAD from MSACCESS it allows me to run a passthrough query to insert rows in a table and then drop the table. Where did I go wrong? I suspect I should start over with this ID. Any help appreciated.

  • April 16, 2008 at 11:56 am

    #803245

    In the Login Properties dialog, choose the User Mapping section.

    In the top (users mapped to this login), select your database and leave the defaults here.

    In the bottom (which just enabled), leave "Public" selected and select the role "db_datareader".

    You should not have to do anything else.

    In addition, if you have to use MS Access as a front-end application (take note that it is not a very good front-end application for SQL Server), you may want to use an Access Data Project (.adp file rather than .mdb file) to avoid the ODBC layer and the used of linked tables. Linked tables from MS Access are likely to cause a lot of multi-user problems and scalability issues.

  • April 17, 2008 at 6:02 am

    #803611

    Thanks very much Michael, I was clearly making this harder than it needed to be. Works fine. I also take on your comments regarding MSAccess and convincing my users to come off Access may be hard but the adp approach could be a good compromise. Will work on that

Viewing 3 posts - 1 through 3 (of 3 total)

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