In our environment, users were granted read/write/ddladmin permissions on tempdb database (dev/qa/prod).
These permissions will be lost when SQL Services restart, as tempdb rebuilds every time. One solution that is currently in place is – reapply the permissions scripts from a SQL Agent job, when ever SQLAgent starts. This is not accurate, as the users/permissions will be modified over time. Another thought, is take a daily backup (with a 15day retention policy) of tempdb permissions and use the same job to pick the latest file to reapply the permissions when SQL restarts. Is this an accurate solution for the problem.
Thanks!!
Best Answer
An alternative to modifying model might be to create a stored procedure in the master database that contains whatever you need to do. Then you mark this as startup proc (sp_procoption).
You might have to put in a WAITFOR in there in case SQL Server executes this before model has been recovered and tempdb has been created. For you to play with.
I've only used startup proc occasionally, so make sure you do thorough testing.
And, I fully agree with J.D.: modify them jobs instead, if at all possible.