We created a SQL Login account for reporting use only (SSRS), we'll call it 'MyReportAcct'.
We've used this account for some time and it is created for every newly installed instance. It has been working as you'd expect, except on a couple of mirrored instances. I've not seen this issue on any standalone instances in my environment.
Randomly, reports will stop working and there is nothing being changed based on our internal processes for Change Management, Windows Logs, and SQL Error logs. Here are two of the error messages we receive:
Upon investigation of the account, it shows up under the Security object folder in SSMS. Also, we find that the account is associated to all the databases under User Mapping for the account properties. I've verified that 'MyReportAcct' has
db_datareader, so on the surface every looks as expected.
The rub comes when I try to alter the account or remove the mappings. I'm met with:
- Cannot alter the user because it does not exists or you do not have permission (Error 15151).
- Cannot drop the user dbo.. (Error 15150)
What I've had to do to fix the issue is delete every instance of 'MyReportAcct' on the server, then recreate it, then add to each database. This doesn't happen all the time, but often enough that it is irritating. Further, we shouldn't have to do this at all.
I think that we should change this account to an Windows Account instead of a local SQL Login in every instance. I have a feeling that this might fix the issue, but I need to prove why or provide a reasonable answer. I think part of the problem is the mirroring in some way, but I don't know how to prove that either.
Security is a weakness of mine and I'm employing steps to change this, so any insight you can provide would be greatly appreciated. Please let me know if you need more information.