Category: SSMS

Link for information

http://technet.microsoft.com/en-us/library/ms165673(v=sql.105).aspx

Simply within SSMS right click on the instance and hit STOP and
To start an instance if you are still in SSMS you can simply right click and hit start

Note if you stop a SQL Server instance from within SSMS AND then exit SSMS and try and go back in you will be denied as SSMS is unable to connect to a service that is not up and running.

In such a case to restart the instance you need to go to SQL server configuration manager and click on the instance and restart from there.
In Windows 8.1 you can get to config manager by using the search facility.

Windows 10 Alternative
Hit the search ring
type in services.msc
A new dialog should appea that will have Services(Local) with several columns – Name / Description / Status / StartupType
You want the Status to be Running
Use the mouse to highlight SQL Server (MSSQLServer) and then right click
Start

Your server should now be running

Recently I installed SQL Server 08 R2 Express.

As part of the standard installation the sa login is disabled as default.

Clearly although this is a useful security feature it is a bit awkward if you are wanting to undertake certain tasks and as part of the installation this security feature is in no way obvious.

So what do you do to re-enable it?

Firstly log into SSMS under the machine windows login.

Go to the Security section of the server (not any of the databases) and expand the Logins branch. SA the system admin should be listed and if it is disabled it will have a small red down arrow next to it.

To re-enable highlight sa and right click

Select properties

Select Status

now alter the following
Permissions to connect to database engine
Set this to GRANT

Login
Set this to Enabled

Now highlight instance itself and right click and go to properties.
Within server properties highlight Security.
And change set server authentication to
SQL Server and Windows Authentication mode.

You should have sa login now enabled – don’t worry if sa still has a small red arrow next to it the SSMS client needs to be refreshed to see changes to set up.

If you want to change the password for the SSMS server I would recommend running the following.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'DifficultPass9£' ;
GO


Note you can enforce the requirement for a strong password within the same security section of the sa login if you require.