Read Only from Access into SQL Server 2005 RRS feed

  • Question

  • Everyone:

    I have a user with Access 2010 that wants to connect into a SQL Server 2005 database.  We would like to limit him to read-only access of the SQL Server data.  Is this something that can be done using a connection string?  What is the correct method of providing read-only access to this data?





    • Moved by VMazur Wednesday, June 22, 2011 10:41 AM (From:ADO.NET Managed Providers)
    Tuesday, June 21, 2011 3:20 PM

All replies

  • Not sure how you are connecting (ODBC? OLEDB?) but security should be set at the database server level and not the connection string. I would post your question to the SQL Server Security forum if you don't know how to configure:



    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, June 21, 2011 5:43 PM
  • For one thing, users can create their own connection strings, so a connection string option wouldn't be reliable.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, June 22, 2011 2:52 PM

  • There are a couple of options. One is to add this user to the database fixed role db_datareader and db_denydatawriter. The latter may the side effect that if he is entitled to connect through some other application that performs updates he will not be table to.

    By default, users have no access whatsoever to any table; they must be granted access one way or another.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 22, 2011 8:51 PM