i'm an accidental DBA for a company and having trouble figuring out this permissions situation.
We have a new 2017 SQL server that I would like to get our report writer / analysts access to a few of the databases. She is in an "analyst" AD group with another analyst. That group is setup at the server level login's with a few of the databases checked off (not MSDB) in the user mapping.
While trying to query the databases she should have access to, she gets the same exact errors described in this post (including the MSDB datababase part), but the other analyst does not: https://www.codeproject.com/Articles/548424/The-Server-Principal-xxxx-is-Not-Able-to-Access-th
We can't find the 2 users setup individually anywhere on the server where one would have different access than the other. We also checked other groups to make sure they were not in them. Is it possible it's a way her computer/SSMS is configured? We changed her default SSMS database to a database she should have access to.
The resolution in the article I linked suggests to grant connect access to the public role or individual users/groups in MSDB but when testing that, she could view data in that DB and had more access than we would like. I thought I read connect should only grant her enough access to connect to the DB but not see anything, however that didn't seem to be the case.
Any suggestions on what we're missing? Let me know what what additional details you may need.
Thank you, Dave