重新引导服务器后，为什么我的tempdb会重置权限? [英] Why does my tempdb reset permissions when the server is rebooted?

问题描述

过去两次我们重新启动了sql server，我们的网站已经关闭.原因似乎是因为重新创建了tempdb，并且ASPState用户失去了读写tempdb的权限(这是一个ASP站点，会话数据存储在sql服务器中)

直到大约两个星期前这才成为问题.有谁知道如何防止重启后sql server重置tempdb权限?还是为什么这只是最近才开始发生?我们正在使用MS SQL Server 2005.

解决方案

首先，您不应该直接向tempdb分配权限.出于显而易见的原因，每次重新启动时都会重新创建它.

实际上哪个提出了一个问题:为什么您仍然需要对该数据库具有直接权限?

除了能够连接到sql server来创建临时表之外，您不需要任何权限.但是，如果您要在tempdb中创建实际表，那​​么我强烈建议您将其更改为为此目的使用专用数据库.

更新
根据马丁的评论，我只能说哇.我什至从未考虑过这将是一个选择.

好吧，现在我已经从休克中恢复了过来.

在按计划执行的sql server中创建一个新作业.该计划应设置为每当SQL Server代理启动时自动启动".该作业应重新创建必要的tempdb权限.

简而言之，当重新启动服务器时，SQL Server代理将重新启动(前提是该服务是以这种方式设置的).重新启动时，它将启动此作业，然后将修复您的权限.我希望该站点比SQL Server完全重新启动所花费的时间仅多几秒钟.

The past two times we have rebooted our sql server, our website has gone down. The reason appears to be because the tempdb is getting recreated and the ASPState user is losing permission to read/write to the tempdb (it is an ASP site and session data is stored in the sql server)

This was not a problem until about two weeks ago. Does anyone know how I can prevent the sql server from resetting tempdb permissions after a reboot? Or why this only started happening recently? We are using MS SQL Server 2005.

解决方案

First off, you shouldn't assign permissions to the tempdb directly. For the obvious reasons that it gets recreated on every reboot.

Which actually raises a question: why do you need to have direct permissions to this database anyway?

You don't need any permissions beyond just being able to connect to sql server in order to create temp tables. However, if you are creating real tables in the tempdb, then I highly suggest you change this to use a dedicated database for this purpose.

UPDATE
Based on Martin's comment all I can say is wow. I would never even have considered that this would have been an option.

Okay, now that I've recovered from the shock.

Create a new job in sql server that executes on a schedule. The schedule should be set to "Start Automatically whenever SQL Server Agent Starts". The job should recreate your necessary tempdb permissions.

In a nutshell, when the server is rebooted the SQL Server Agent will be restarted (provided the service is set that way). When it restarts it will kick off this job that will then fix your permissions. I'd expect the site to remain down for only a few seconds more than it takes for SQL server to completely restart.

这篇关于重新引导服务器后，为什么我的tempdb会重置权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！