Today, opening the Global address book form presented me this nice error: Permission denied in database ‘TempDB’. And this is not the first time this one surfaced, but most of the online solutions are not entirely correct. So let’s explain this one.
Doing an online search will show you that the reason of this is simple, the AOS has no permission in the TempDB as the error states. But why? Well, the first important thing you need to keep in mind is that the TempDB database is recreated when the SQL Server is restarted. Thus, something will need to reapply the needed permissions for the AOS after a restart. Most online solutions show you how to apply these permissions, others even schedule a T-SQL command to run periodically to do so!! But this is not how this is intended to work.
When installing an AOS, the installer will create stored procedures in the Master database to set the needed permissions on the TempDB. These procedures are called by the AOS when SQL has been restarted and appropriate permissions are missing. And this is where the problem mostly comes from. When the account running the AOS service has been altered, these stored procedures haven’t and they still have the old AOS user account. This means, when they are called to grant permissions to the TempDB, they do so for the wrong AOS account!
Altering these to contain the correct AOS user account will solve the issue in case of a SQL reboot.