Get Started using SSMS - Connect, Navigate, Query

By:   |   Updated: 2022-03-18   |   Comments   |   Related: > SQL Server Management Studio


Problem

You're new to SQL Server and have seen how to install SQL Server Management Studio (SSMS) and connect to a SQL Server with it here: SSMS Download, Install and Configure with GUI and Command Line. Now you're ready to get started using this IDE for database administration and development.

Solution

SSMS is an extremely feature rich and powerful management tool for DBAs and Developers working on MS SQL Servers. The sheer number of features can easily be overwhelming, but don't be intimidated by that. In this tutorial, we'll look at some of the fundamental and commonly used functionality. The goal is to help you get started and build some familiarity before moving on.

We'll be using SSMS 18.11 running on an on-premises Windows Server 2019 operating system Virtual Machine and will connect to an SQL Server 2019 SQL Server which are all the latest versions as of the time of writing. You can use this version of SSMS to connect to older versions of SQL Server as well, so the information below should apply to SQL Server 2005 and later.

First thing we'll do is look at some options for connecting to a SQL Server.

Starting SSMS

If you created a shortcut to ssms.exe you can click on that, or open it from the Start Menu, or:

  1. Go to Search Box
  2. Type in 'sql server management studio'
  3. Click Microsoft SQL Server Management Studio
Search for SSMS

Connect to the SQL Server Database Engine

You'll first be prompted with the 'Connect to Server' window.

There are two ways to authenticate to a SQL Server instance. The first is with Windows Active Directory (AD). This is the preferred authentication method. It's more secure and is managed from Active Directory.

  1. Default Server type is Database Engine
  2. Enter the SQL Server name you're connecting to
  3. Default Authentication is Windows Authentication (Notice 'User name' is filled in and both 'User name' and Password are grayed out. This is because SSMS is using the AD credentials you're logged into your Windows machine as.)
  4. Connect
Connect to Server 1

You're in and are now connected to the SQL Server using your Windows AD account credentials.

The other way to authenticate is with SQL Server Authentication (commonly called just SQL Auth). With SQL Auth, authentication is performed in and is managed directly on the SQL Server database. You may be using this method if your users aren't on a domain or possibly with a legacy application that doesn't support AD authentication.

  1. Connect
  2. Database Engine
Connect to Another Server
  1. Server name
  2. Select SQL Server Authentication from the Authentication dropdown
  3. Enter SQL Server Authentication login
  4. Enter password
  5. Connect
Connect to Server 2

We are now logged in using SQL Auth and have a second connection to the SQL Server.

  1. Disconnect (we'll be using the Windows Authentication connection for the rest of the tip)
Disconnect

Other SSMS Connection Options

It's out of scope for this tutorial, but this is a good time to digress slightly and point out a few things you may have noticed and are curious.

First, the other three selections under the Authentication dropdown are different methods of authenticating to an Azure SQL Database.

  • 'Azure Active Directory – Universal with MFA' – connecting with Azure Active Directory credentials and multi-factor authorization
  • 'Azure Active Directory – Password' - connecting with Azure Active Directory credentials
  • 'Azure Active Directory – Integrated' – connected with using Azure Active Directory credentials from a federated domain
Other Authentication Methods

And lastly for this section there are some less commonly used options are here.

  1. Options >>
Other Connection Options

Here we can choose a database to connect directly to (used primarily for Azure SQL Database), change some network options, choose some encryption settings, and set some color options.

Connection Properties
  1. Always Encrypted
Always Encrypted
  1. Additional Connection Parameters
Additional Connection Parameters

Lastly, those other Connect options are to connect to:

  • Analysis Services (SSAS)
  • Integration Services (SSIS)
  • Reporting Services (SSRS)
  • Azure Storage
  • Azure-SSIS Integration Runtime
Other Servers

SSMS Functionality with the Database Engine

Let's get back to the where we were.

After you connect, the first place you'll generally start in is the Object Explorer to browse the database objects.

  1. Expand the server dropdown
  2. Expand the Databases dropdown
Object Explorer

The more commonly used items under the Database dropdown are Tables, Views, Programmability, and Security.

  • Tables and Views - manage Tables and Views
  • Programmability - manage Stored Procedures, Functions, Triggers, etc.
  • Security – manage permissions for Users, Roles, Schemas, etc. (database level)
Databases Folder

Under the Security folder dropdown under the Server (not to be confused with the Security folder under each database) is where you manage Logins, System, Roles, etc. Note, Logins give you access to the SQL Server and Users give you access to the Database.

Security Folder

The Management folder is where Maintenance Plans, SQL Server Logs (one place to look), and Database Mail are managed.

Management Folder

Under SQL Agent is where Agent Jobs, Alerts, Operators, Proxies are managed, and SQL Agent logs viewed.

SQL Server Agent Folder

There are a couple of ways to open a Query Window.

Click the 'New Query' button or

  1. Right click on any Database (you'll be brought directly into the database you right click on)
  2. 'New Query'
New Query

The query window will open. Some points of interest are shown. You can select a Database from the dropdown in the ribbon. In the Query Window you can:

  • Select zoom in or out on the text
  • See your SQL Server name and version
  • See who you're logged in as
  • Get your database context
  • Get the time it took a query to run
  • See how many records the query returned
Query Window
  1. Enter your Query in the window
  2. Execute (or hit F5)

The result set is returned.

Running a Query
Next Steps

You've taken the first steps to using SSMS. Now, here are just some of the SSMS Tips with more information you can find on MSSQLTips:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-03-18

Comments For This Article

















get free sql tips
agree to terms