Accidental SQL Server shutdown

Question

Accidental SQL Server shutdown

mark anthon

SSC Enthusiast

Points: 100
More actions
January 25, 2006 at 10:12 am

#90519

We are concerned over an incident from yesterday. Luckily it was a development environment, but SQL Server was accidentally shutdown. Does anyone have a suggestion for how to prevent this?
We theorize that someone (with admin rights) was in Enterprise Manager and accidentally right-clicked on the server and accidentally clicked on Stop. The frustrating part is that when deleting a database, at least you get a challenge that asks if you're sure you want to do this. But when shutting down the entire environment, SQL just lets you do it. We see that as a flaw. My idea was to create a user that has everything but the power to stop services. Thanks in advance.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

David Jansen-278311 SSC Enthusiast Points: 121 More actions · Answer 1

Add the user to a fixed database role that has just enough permissions to accomplish their tasks. db_datareader and db_datawriter are a start for select, insert, update, delete permissions. It sounds as if the user is a domain or local admin and used Windows authentication to logon to the MSSQL server. If need be, don't use mixed mode authentication. Stick with SQL authentication and put the users in server/database roles asap.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

That's one way. Another way is someone went into services and accidentally shut it down that way if they possess admin rights. Unless SQL Server Agent was also running they'd get no prompt.

When you say, "everything but the power to stop services," do you mean server level functions like creating user accounts? Or do you mean basically dbo on all databases?

K. Brian Kelley
@kbriankelley

mark anthon SSC Enthusiast Points: 100 More actions · Answer 3

Yes, we definitely need to create logins/user accounts, create backups, create/restore databases, etc.

Also, do we know if SQL Server 2005 has modified the "stop" option to perhaps, at least, challenge a user when attempting to stop SQL Server?

Greg Burns Mr or Mrs. 500 Points: 559 More actions · Answer 4

I get a "are you sure message" in 2005's Server Managment Studio. But then again, I also get a similar message in EM against a 2000 database. You don't?

mark anthon SSC Enthusiast Points: 100 More actions · Answer 5

We get the "are you sure?" challenge when attempting to delete a database in EM, but not when stopping SQL Server.

I'm very happy to hear that you're challenged in 2005's Server Managment Studio. Currently we're not running 2005, but will be migrating to 2005 in the next twelve months.

Greg Burns Mr or Mrs. 500 Points: 559 More actions · Answer 6

Wonder why you don't see it in EM when stopping it? Odd. It definately should. I would search around and see if anybody else has an answer to that.

If you owned a copy of 2005, you could start using SMS against your 2000 servers to work around whatever bug you are seeing.

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 7

If anyone knows the exact permissions needed in EM to stop MSSQL I would also like to know. One day one of our SQL Servers mysteriously stopped and I asked a database owner to see if he could stop the service from EM and he was able to. Another user who is also only a database owner was able to start it in EM. No kidding. Neither person was in any server roles either. It has puzzled me ever since.

Linda

swjohnson Hall of Fame Points: 3254 More actions · Answer 8

Now remember it may not have been from Enterprise Manager, you do have a T-SQL statement called "SHUTDOWN" that you can execute from Query Analyzer or you can issue it from a command line on that machine "net stop mssqlserver"

From BOL: SHUTDOWN permissions default to members of the sysadmin and serveradmin fixed server roles, and are not transferable.

The Net Stop command can be executed by those with Admin rights on the Server if I remember correctly.

Therefore, you will need to limit who is in the Admin group in both SQL and Windows.

Also, you should be able to check the Windows Security Log on that machine to see who was signed in to it about the time it was shut down. (and then give them a good flogging!)

Finally, and I hope this wasn't it but since the T-SQL statement SHUTDOWN exists, you need to watch out for SQL Injection that would be inserted and in essence run this command and shut your server down.

SJ

Accepting all invites

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 9

I watched the user stop MSSQL from EM, but I think you just shed some light on the subject. I believe that both users are local admins on the server. That explains the NET STOP. Thx. 🙂

Linda

jasonhatfield01 Right there with Babe Points: 722 More actions · Answer 10

I'm not entirely sure, but I think even members of "Power Users" (OS role) can stop services. Administrators will for sure.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

Yes, anyone who is a member of the local Administrators or Power Users groups have the ability to start and stop services.

K. Brian Kelley
@kbriankelley

Curtis Tamanaha Right there with Babe Points: 725 More actions · Answer 12

I had no idea you could shutdown SQL Server from Management Studio or EM.