I am using MS Access 2007 to generate reports/queries on data stored on a networked machine running MS Sql Server. There are two accounts (among others) registered on the sql server that users can use to connect to the database. One allows all network administrators to connect using a Trusted Connection with NT Authentication, the other uses the SQL Authentication with a user name and password.

Physically in front of the sql server, performing queries on a particular table under both accounts yield the same results; however, when I try to link this particular table into MS Access, I lose the [cost] field from the table using SQL Authentication, while it has no problems when I link using the Trusted Connection with NT Authentication. Any help or insight to how to fix this problem would be greatly appreciated.

As an aside, I've thought setting up another account that lets all users connect using a Trusted Connection with NT Authentication, unfortunately there are a lot of individual privileges set up for the current user, and without an easy way to export and import those privileges into a new user in Sql Server, it's just not worth it, especially when it's probably just some easy option that I'm missing somewhere.

Permissions to a SQL Server db table aren't really field based. There isn't a situation where one connection (with XX permissions) shows a specific field in a table while another connection (with XY permissions) doesn't show that specific field in that same table. (although you could have a SQL View where it shows that field in one SQL View query and doesn't for a different SQL View.)

If your 'cost' field though is in a different table and some specific users don't have permissions to that table itself, then it would be a matter of adding the users for permissions to that table.

See, that's what's so weird about this problem. Although, it looks like for that field under user A, there is no datatype specified for the 'cost' field, while it IS specified for user B...which makes no sense why it would be set up that way. I'm more of like a 3rd-party user of the SQL tables, ect...it was set up before my arrival, and then I came on the scene, and am trying to clean up what was screwed up previously. Could the missing datatypes cause ms access to not want to show it when linked?