July 14, 2008 at 2:56 am
Hi!
I'm having a problem, that when I restart the SQL Server permissions from tempdb are lost. I have set insert, delete, update and select in tempdb / schemas / dbo. I have done this because of getting a internet-site to work correctly. The problem is that when an update is done to server and the computer is restarted the only permission left is Connect for tempdb / schemas / dbo which result in error on the Internet site. (when user logs into it)
I don't know why. Can anyone give me a hint in right direction so I know where to look?
July 14, 2008 at 3:34 am
TempDB is completely recreated every time SQL starts. What you're seeing is normal and expected.
If you want permission changes to persist through a restart, you'll have to set them on the model database, as that is the template used to recreate tempDB.
I am most curious though, why do you find it necessary to assign permissions to tempDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2008 at 3:53 am
Aha. I'm searched around on the net a bit now and you're so totally right. I'm using ASP.net with sessionState = SQLServer and as default it seemed to used tempdb but I've found now where I could change this. ASP.Net need these insert,delete etc-permissions...
Thanks for your helkp!
July 14, 2008 at 10:25 am
Have a job for this, with all necessary permission that are needed on tempdb for required account and have this job scheduled to start when ever sql server starts.
July 15, 2008 at 12:04 am
Hi!
Yes, that could be a way of doing it but there's a script in the .net-framework that replaces the use of tempdb to another database and with that other database the permissions doesn't get lost at a restart.
July 15, 2008 at 6:44 am
GilaMonster (7/14/2008)
TempDB is completely recreated every time SQL starts. What you're seeing is normal and expected.
Gail already answered that question. Your user databases do not get recreated on restart so any permissions assigned prior to restart will be there afterwards. TempDB does get recreated. Therefore, any permissions assigned before the restart are lost.
Regards,
Terry
-- You can't be late until you show up.
July 17, 2008 at 1:01 am
Just a tip ::)
When SQL Server is installed the setup program creates tempdb database. Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, subqueries, and aggregates with GROUP BY, ORDER BY, for cursors and so on. Tempdb database contains only temporary objects, so if you want to create a permanent object, do not create it in the tempdb database.
After you have installed SQL Server, you can find tempdb.mdf file with the size of 8.0 Mb and templog.ldf file with the size of 0.5 Mb in the directory C:\MSSQL7\DATA (path by default). These files contain tempdb database.
Now tempdb database can automatically grow, so not necessary to make tempdb very large. Autogrow feature is set by default. Each time the SQL Server is started, tempdb database is reset to its default size. Automatically growing results in some performance degradation, therefore you should set a reasonable size for tempdb database and a reasonable autogrow increment to avoid tempdb automatically growing too often.
thanks;)
July 17, 2008 at 6:12 am
rinu philip (7/17/2008)
Just a tip ::)When SQL Server is installed the setup program creates tempdb database. Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, subqueries, and aggregates with GROUP BY, ORDER BY, for cursors and so on. Tempdb database contains only temporary objects, so if you want to create a permanent object, do not create it in the tempdb database.
While I agree with your statement, Microsoft's Great Plains product (version 9) actually stores two small tables in tempDB, which are recreated each time the SQL service is restarted. Could be a holdover from before they purchased the product as it doesn't conform to their intent for tempDB. Sort of ironic, isn't it?? :w00t:
-- You can't be late until you show up.
July 17, 2008 at 6:49 am
hmm..thats interesting;)
July 18, 2008 at 8:17 am
I used to manage an application that needed its users to have db_ddladmin rights in tempdb.
I created a job that ran when Agent was started to create these rights. Effectively this meant the rights were created whenever SQL started.
I know I could have added the rights to model and they would havebeen picked up by tempdb when SQL started, but I did not want the users to have ddladmin rights in any other DB that got created.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply