SQL Server Management Studio is an Integrated Environment that allows the user to manage, configure, administer and develop in Microsoft SQL Server.

The latest SQL Server Management Studio (SSMS) is free and can be downloaded from this link. Note that this version of SSMS supports only 64-bit Windows Operating Systems. If you need to use SQL Server Management Studio on a 32-bit machine, then you can download the 32-bit version of SQL Server Management Studio Express Edition here.

The below screenshot shows the fist screen of the latest version of SSMS at the time of the writing, which is SSMS 17.2.

What is SQL Server Management Studio - Article on TechHowTos.com

As you can see, right after you start the application, you are presented with the login screen.

In the login screen, you need to specify at least two parameters (if you need, you can specify more options in the “Options” dialog), these are:

  1. Server Name
  2. Authentication

In the “Server Name” field, you can enter the name or IP of the SQL Server instance to which you want to connect to.

In “Authentication” you can specify one of 5 values:

  • Windows Authentication
  • SQL Server Authentication
  • Active Directory Authentication with MFA Support
  • Active Directory – Password
  • Active Directory – Integrated

Active Directory – Password authentication, Active Directory – Integrated authentication and Active Directory Authentication with MFA Support are used only for connecting to Microsoft Azure SQL Database.

Normally, in the case of on-premises SQL Server instances, regarding authentication, you either select “Windows Authentication” or “SQL Server Authentication”.

If you choose Windows Authentication, then SSMS will try to log on to the target SQL Server instance within the context of the user that runs SSMS. Usually this is the Windows user you are logged in to Windows, unless you launched SSMS within the context of another user (i.e. using the “Run as different user” option when launching SSMS).

If you choose SQL Server Authentication, then you will need to specify a username and password for connecting to the SQL Server instance. In both cases, you will need to be granted access by your Database Administrator (DBA) in order to be able to access the SQL Server instance and consequently the database.

The other three authentication types will be covered in another blog post where we will be talking about Azure SQL Database.

Now that the authentication part is clear, let’s login with Windows authentication and see a screenshot of SQL Server Management Studio for a sample SQL Server Instance:

What is SQL Server Management Studio - Article on TechHowTos.com

As you can see in the above screenshot, right after you connect to a SQL Server instance via SSMS, in the left, there is a tree via which you can access just about anything. For example you can access, view and edit, : databases, tables, views, stored procedures, functions, users, logins, etc. Also, you can access SQL Server Agent jobs. Moreover, you can create databases as well as configure different SQL Server components and features. All these, will be covered in subsequent posts.

If you click on “New query”, you will be presented with the below screen, where you can start working with T-SQL:

What is SQL Server Management Studio - Article on TechHowTos.com

As you can see, in the above example, I’m querying a table called “dSampleSales” that exists in a database called “SampleDB”. Right after I’m running the query, the results are displayed in Grid view.