0

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

New contributor
David Squires is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Is the analyst in some other AD group that has a login on the server? Check to see if any logins have msdb as default SELECT * FROM sys.server_principals WHERE default_database_name = 'msdb' – Tony Hinkle May 21 at 17:43
  • Thanks Tony- there are none with the default of MSDB. I have looked through the AD groups on the server (there aren't many) and the analyst is not in another AD group with access. – David Squires May 21 at 17:58
  • Did you click "Options" on the SSMS connection dialog to check to see if the msdb database is specified there as the DB to connect to? Also check the additional parameters dialog as putting "database=msdb" there would cause this. – Tony Hinkle May 21 at 20:58
0

There are several things that could cause SSMS to attempt to connect to a certain database at logon:

  • If the user is in an AD group, and the login for that group has the msdb database set as the default. If the user belongs to multiple groups that have logins, I don't know which login it uses to decide which database to connect to, but nonetheless it will use one of them.

  • On the connection dialog in SSMS, click the options button and note that the database to connect to can be specified. This setting is persistent, so if it was set to msdb in the past, it stay that way for the user until changed.

  • On the connection dialog in SSMS, click the options button and select the Additional Connection Parameters tab. This setting is also persistent, and if "database=msdb" was specified there at some point, removing it will resolve the issue.

| improve this answer | |

Your Answer

David Squires is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.