I know how to set up an import using Access to retrieve info from our SQL Server via ODBC as long as I use the 'sa' account. However, I have an accountant who knows his way around Access and so would like to be able to import data from our SQL server so that he may manipulate the data and create his own reports in Access.
Now, I of course do not want to give him the administrator account to login and access it. So, I went into Enterprise Manager (v8.0), selected his user login, and assigned him to a db_datareader role, figuring he would then be able to read/import data from the tables without giving him any kind of other administrative rights. This, however, didn't work and the Access tells me it can't log in his user name.
I'm not sure what permissions I must give him in order to allow him to properly import data from SQL Server without giving him too many administrative rights. Any guidance with this problem would be great!