0

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!!

2

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.

| improve this answer | |
0

If you read the top accepted answer on the StackOverflow you linked, it takes a step back and asks the question "why are you creating permissions for users on TempDB"? They're unnecessary for users to create, access, and manage temporary objects in it. The permissions you mentioned are already implicitly granted to all users with Connect access to the server, for temporary objects. So you shouldn't have to worry about TempDB being recreated.

(The only time these permissions matter is if you wanted your users to create non-temporary objects in the TempDB database which wouldn't make sense and be a bigger issue to manage than the permissions themselves.)

| improve this answer | |
  • yes, there are some jobs which creates tables in tempdb and also they refer these tables while applying data patches. I know, this is a kind of mess as this DB is migrated from Sybase to SQL Server. And still some of the SP's use tempdb to create tables. – Kris Nov 20 at 2:50
  • @Kris I'm assuming you can't alter the jobs to create temp tables instead or create real tables in a different database? – J.D. Nov 20 at 2:55
  • no, i can not. these were created initially in sybase, as in sybase there is an option to create multiple and dedicated tempdb db's..so, they designed the sp's which fill these temp tables with data in temodb. – Kris Nov 20 at 3:01
  • Personally, the full stack developer in me would want to rewrite the jobs / SPs to not do that lol. That being said, if you're on a server where you don't intend to create other databases or you can isolate this system to its own server, then you can alter the model database to have the objects and permissions you need. That is the database TempDB (and all DBs) are modeled after when they are created (so every time TempDB is recreated, it should have the permissions setup the way you need.) – J.D. Nov 20 at 3:55
  • 1
    thanks J.D. and Tibor ....this is a legacy application and code transformation is in progress...not sure how much time they take to do that...meanwhile being a DBA, i should get things go on...i think going with a startup proc could be a good option rather than couple of jobs to backup and restore..thanks for your inputs.. – Kris Nov 20 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.