Using ODBC & SQL Server

[jaspoe]

Hi all,

Quick question: I'm trying to understand using an ODBC connection through MS Access and Excel just a little bit better. Our ERP vendor uses MS Access to report, and when it's installed, they create an ODBC connection to the database. The ODBC connection is set to use SQL logins, not Windows Authentication (they create their own SQL logins when the server component is installed). Users in our company have used Access to pull in data from SQL, and have used the ODBC connection. In that case they must use Trusted Connection, and then data will only pull if the logged in user is a member or in a group that is a member of the database? I thought this was the way it was supposed to work, but I've ran into a few issues that made me rethink this. I made some security changes by removing one Windows group from the database, and added another the same users are members of, and it killed their access to the SQL data. Changing groups on the SQL database shouldn't deny access to linked tables in an Access MDB should it? Also, is there a way to limit the returned results to only db tables and not views/sprocs?

Sorry for the mundane questions, I've done a lot of searching, just trying to find a clear cut answer.
Thanks,
Jason

 

[FoFa]

Did you verify the permissions on each object when you swapped out the groups?
Are the groups part of a ROLE you did not add them to?

 

[jaspoe]

Thanks for the reply. I've made sure the new group mirrored the permissions of the old group, and that is what I'm having trouble with. The old group was actually Domain Users that had datareader, and a new group with a few individuals who actually needed direct access to the database were the only ones in the group. Once the change was made, their Access apps weren't able to connect. I had to put back the Domain User group for the time being and they connected just fine again.

I'm testing this further in my test environment, and so far everything looks like it is behaving as it should. I'm matching the new group to roles it should have with the same roles the old group has. My understanding of the Access apps was shaken a little when they were denied access. I just need to be completely sure of what is happening before I try to push out a change like this again.

Thanks.

 

[FoFa]

It sounds like it is totally SQL permissions.
Of course Access is passing the permissions, so you should see what access is using to connect to the SQL server with.

 

[jaspoe]

That's the way I'm starting to lean now too. I've ran SQL Profiler and verified the login being passed, and it definitely is passing the current logged in user. I've still got some testing to do, but I don't think Access or ODBC is the problem. Thanks for the comments.

 

[jaspoe]

Just figured it out, I used SQL script to create my new groups, and when they were created, they were disabled. Not completely sure why yet, I didn't have any DISABLED statements in there. Some groups were created ENABLED though. Once I enable those accounts, I think I'll be good to go. So it was SQL all the time, like you suggested. Thanks for taking a look.
Jason