You only need to allow access the the user db, and optionally place them in a user-defined role. For the SP in that db that creates a temp table, just grant execute permission to the user, or to public (the lazy way ), or to the user-defined role. No tempdb permissions are required.