Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GregDDDD
Posting Yak Master
120 Posts |
 Posted - 2010-04-14 : 15:00:37
|
| We are moving from SQL Server 2000 to SQL Server 2005. This is a third party database with a client front-end that is used by 30 users. Then, we do all of our custom reporting using MS Access and linked tables to the SQL Server. With the new implementation I was going to create new ODBC connections for the user using SQL Native Client and Windows Authentication. In the past we just used SQL Server authentication and the 'sa' and no password. We now have a strong password on sa.I'm using a test domain account that is set up for a typical user and added a login for them in SQL Server 2005. Under user mapping I select a database and set database roll as db_datareader.They can link to the tables in Access, but the odd thing is, they can not see all of the columns in the table. The first table I looked at had 15 columns, but they could only see 6 in the Access table view. It was similar for other linked tables. When I made them db_owner and db_datareader, and then re-linked the table they could see everything. When I tried db_owner and db_accessadmin the view went back to only the 6 columns.I should add that I am linking to views and not tables.Does anyone know why this would be? I would think that db_datareader should be able to see the entire view if they are able to link to it.Greg |
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-04-14 : 18:06:42
|
| It must be something with Access 03. When I have that user log in to SQL Server Management Studio with only db_datareader she can see all of the columns in the view. |
 |
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-04-14 : 18:26:41
|
| After playing around with it a bit more, db_ddladmin and db_datareader seemed to give me what I need.Greg |
|
|
|
|
|
|
User Mapping