Last night I moved a database from Server Q (QA) to Server P (prod). Part of the process was to remove some QA logins we use and replace them with production logins.

Things seemed to go well but during testing a job running as Login F failed with error 18456. Simple, it wasn't on the list of logins sent to me to provide access to. We changed the job that runs on a separate server to run under another login and it worked.

Seems simple, but investigating I found that this login F didn't have access to the database in our QA environment either. It was set up as a database user for another database and we have cross database permissions enabled.

Checking Server P (prod), it's set up the same way. So I'm trying to figure out why a job running as this database user worked in one environment but failed in production where the configuration seems to be the same.

  • What's the state of the login failure? Just because it doesn't have a direct login name doesn't mean it can't login due to group membership if groups have logins. – Sean Gallardy - Retired User Sep 11 '19 at 15:39
  • Error: 18456, Severity: 14, State: 38. Your average login denied error because an account doesn't have access to a database. I checked the group access between the two databases in both environments and they look the same. – Alen Sep 11 '19 at 15:42
  • Any role based security? – Jacob H Sep 11 '19 at 15:47
  • You need to get the exact roles and access of login F to the user database from QA database and see what is missing. Check this link, it could help --> dba.stackexchange.com/questions/36618/… – Learning_DBAdmin Sep 12 '19 at 5:31
  • thx, I have a similar script I use to script out users and logins for permissions auditing. The only difference in permissions I can think of is that the Domain Users group doesn't have access to the database that was moved to production and this may have caused it. But otherwise the permissions of login F look the same on both ends. Not sure if it's in any groups – Alen Sep 12 '19 at 13:06

Your Answer

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

Browse other questions tagged or ask your own question.