locked
Read only System DSN allows writes, how ? RRS feed

  • Question

  • In SQL 2008 R2 I've created a new login that is in the public Server Role, and against a specific DB only has the db_datareader role, so in theory a System DSN created using this user will be read only against the specifc DB ?.

    I've created the System DSN and in Access 2007 I've linked to a table but it allows me to add rows to the table, this should not be possible ?, how is this being allowed ?.

    Thanks.

    Thursday, April 12, 2012 4:17 PM

Answers

  • Hi Fatboy,

    db_datareader: Members of the db_datareader fixed database role can read all data from all user tables.
     
    db_denydatawriter: Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

    Open SQL Server Management Studio, Click a database and Security, right click a user, you could see Role Members in General tab, choose db_datareader and db_denydatawriter.

    For more information, please refer to Database-Level Roles: http://msdn.microsoft.com/en-us/library/ms189121(v=sql.105).aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Tuesday, April 24, 2012 8:41 AM
    Tuesday, April 17, 2012 7:37 AM

All replies

  • are the changed data written back to the table in the SQL Server or do you get an error when closing the table ?

    Have you verified that it does use the credentials defined in the System DSN and not using another login?


    Thursday, April 12, 2012 8:01 PM
  • Hi,

    Try giving both db_datareader and db_denydatawriter roles 


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, April 13, 2012 2:52 PM
  • 'db_denydatawriter'... how the heck did I not see that ? (I'm blaming old age and lack of sleep).

    Will try that right now.

    Friday, April 13, 2012 3:20 PM
  • Hi Fatboy,

    db_datareader: Members of the db_datareader fixed database role can read all data from all user tables.
     
    db_denydatawriter: Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

    Open SQL Server Management Studio, Click a database and Security, right click a user, you could see Role Members in General tab, choose db_datareader and db_denydatawriter.

    For more information, please refer to Database-Level Roles: http://msdn.microsoft.com/en-us/library/ms189121(v=sql.105).aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Tuesday, April 24, 2012 8:41 AM
    Tuesday, April 17, 2012 7:37 AM