March 4, 2015 at 12:56 am
Okay,I post this question cause I had this call yesterday & it stooped me.
I have 2 users (let say A & B) who both have access (no pun) to a database on one of our SQL Server Instances via ODBC connection, they use Microsoft Access to get to the data,following things apply
User A has db_datareader & Role_A as rights on the database
User B has db_datareader,Role_A & db_datareader (user owned schema) as rights on the database.
I have at this time not being able to reach user B but from the info I have the ODBC setting for the connection is the same on their systems.
User B sees the tables & views he needs to see.
User A sees that database dynamic management views BUT not the tables & views
Anyone know what could be going on,I haven't used Microsoft Access in over a decade & these users are located quite far away from me,physically & timezone wise.
March 4, 2015 at 7:45 am
ODBC and Access in particular are pretty picky about who can see what in a database. It would appear that Role_A may not have rights sufficient to let the user see the tables and views. You didn't indicate what version of SQL Server is being used, but from 2005 forward, we typically find it necessary to associate a user's rights with a schema, and we typically make it dbo because we expect the users to need to update tables. If that need doesn't exist, you may want to define a more restricted schema that they belong to. I suspect if User A is given the db_datareader permission as a user owned schema things should work. A final question - has this worked in the past and suddenly stopped working, or has User A just been added?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 5, 2015 at 3:49 am
OK the issue was solved
User_A default database was master
User_B had an other default database
This was the first time this issue came up,but I wasn't under the impression this person was entirely new to the environment he was trying to access
There is currently a migration going on with some of our SQL infrastructure that being done above my paygrade as it were
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply