1

I am connecting to SQL server 2008 R2 from a remote machine, but when i right click on the instance in object explorer it shows me Start,Restart,Stop, Pause, Resume options disabled

I am connecting to default instance on server "DEXTER" [ip: 192.168.1.2] from machine "SPARTAN" [192.168.1.10] in the same network

Aamod Thakur
  • 111
  • 1
  • 5

3 Answers3

2

This means that the user you are currently logged in as does not have the SQL server "sysadmin" server role, and/or has not been granted the server-wide GRANT option "CONTROL SERVER".

One of these is required to control the MSSQL process.

adaptr
  • 16,576
  • 23
  • 34
  • I realize this is an old post, but I'm trying to get this to work with SQL 2008 R2. I'm connecting remotely with my Windows account and I am a sysadmin, but my `start, restart, stop, etc` options are disabled when I right click on the instance from Management Studio. I had to change the instance to "Mixed Mode" auth, but I can't seem to restart it. Any ideas? Thanks – Adam Plocher Oct 01 '12 at 22:23
1

If you logged as Sql Server user and have the sysadmin rights - then you can stop the instance by SHUTDOWN command. But you can only start the instance if you have such rights on host where sql server resides.

Oleg Dok
  • 261
  • 2
  • 5
-4

If all options are disabled, SQL has stuck in some process and can not be restarted easily. If there is no sensitive data - try killing SQL processes and then start it again.

alatar
  • 32
  • 1
  • 1
    Um.. no, SQL server will not be aware of such a problem until you actually try to stop or restart it. If all options are disabled *before* trying to perform these actions, you lack permission. – adaptr Jan 17 '12 at 12:19
  • -1 This is just not right and a dangerous practice. There are plenty of things to look at to determine whether or not SQL is stuck somewhere, which rarely happens anyway. Management Studio menu options being grayed out should never be cause to think that SQL is dead. This problem, as mentioned by Oleg Dok and adaptr, is a permissions issue. – squillman Jan 17 '12 at 14:12
  • The most likely cause for the service control options of the SQL server management studio to be greyed is NOT that the service is crashed or locked. In fact, it's pretty damned unlikely. The most likely cause is that the user do not have the necessary system-level access to the server to control the service. – Stephane Jan 17 '12 at 14:37