SQL Server Performance

User security/read-only access

Discussion in 'SQL Server 2005 General DBA Questions' started by semje, Sep 20, 2010.

  1. semje Member

    Hi,
    Currently running sql 2005 sp3 x64 enterprise edition in a cluster (2 nodes).
    I created a user with only select access to one table and one view. The user is a member of public but is not datareader or a member of any other role.
    When I log in as this user I can not insert, update or delete data (which is what I expected).
    Here is where is gets interesting...
    I created an ODBC connection using sql authentication and the user I mentioned above. Then, I went into access and created a linked table using this ODBC. To my surprise I tried to modify the data from within access and it modified the table on the backend sql server db.
    Any thoughts?
    Thanks,
    Jeff
  2. ashish287 New Member

    Did you make file DSN to connect? And when you configure this DSN, did you configured it with the account you have provided read only access because by default it connects using windows authentication.
    Becuase this is what I tried to replicate your problem, and I am not able to see any of the tables which I secured from this user.
  3. semje Member

    Thanks for your response. In response to your question, yes, that is how it was setup. I just was able to fix the problem. What I noticed is when I created my access db the file format was showing "Access 2000 file format". I deleted the db then went into the options and chose 2002-2003 file format recreated the linked table with the same ODBC and everything worked fine.
    thanks again,
    Jeff

Share This Page