I have SQL server login with sysadmin role, but I cannot stop and start SQL server when I connect from remote management studio, just from local server management studio. How can I enable it?
I use windows server 2008r2, SQL server 2008r2.
SQL Server 2008 R2 – Enable Sysadmin User to Restart Server
sql serversql-server-2008-r2
Related Question
- Sql-server – How to add a linked server to a sql server management studio
- SQL Server – How to Connect to Server with IP Address Running SQL-Express Instance
- Sql-server – Create New Database user to login to SQL Server Management Studio
- Sql-server – SQL Server Management Studio DAC connection error
- Sql-server – How to remove sysadmin from sa login in sql server
Best Answer
If you connect to SQL Server from a
Machine Mwhich has SSMS installed but your account is not local admin on Machine M you would find the option of start/stop to be disabled or greyed out. OTOH if you are local admin on the Machine M you would find the option to be enabled. When you do this from server on which SQL Server is installed, you are administrator there and hence you see the option. It does not matter whether your account is admin in SQL Server or not when you connect from machine where you do not have local admin privileges you would see the option disabled, however you would still be able to perform all sysadmin activities in SQL Server. This is not a complete statement their could be other reasons as well, this is what mostly I have observed, please check and let me know. Please also refer to SQL Server agent start stop and restart disabled blog to know more about reasons why this could happen