It is an AX requirement that you have the username running the service account to have db_datareader, db_datawriter and db_dlladmin role membership for tempdb, else AX complains. (Microsoft][SQL Server Native Client 10.0][SQL Server]CREATE TABLE permission denied in database ‘tempdb’.)

Every time your SQL server reboots the server recreates the TempDB database on startup to clear all elements from the database which in turn drops the permissions created previously.

To solve this problem you can either assign the correct rights to the Model database, which is like a template database. Unfortunately this means that every new database gets created with the rights assigned to Model. (kb307487)

Alternatively, you can schedule a script to update the rights when the server starts (substitute the <Username> with the domain\username of the service account):


USE tempdb;
EXEC sp_grantlogin '<Username>';
IF ((SELECT COUNT(*) FROM master..syslogins WHERE name = '<Username>') <> 0) EXEC sp_grantdbaccess '<Username>';
ALTER USER [<Username>] WITH DEFAULT_SCHEMA=dbo;
EXEC sp_addrolemember 'db_ddladmin', '<Username>';
EXEC sp_addrolemember 'db_datareader', '<Username>';
EXEC sp_addrolemember 'db_datawriter', '<Username>';
GO