What Is SQL Server Management Studio (SSMS)?

Posted on by Ian

SQL Server Management Studio (SSMS) is a graphical user interface (GUI) that can be used to administer SQL Server.

Screenshot of SQL Server Management Studio (SSMS) in SQL Server 2014.
SQL Server Management Studio (SSMS) is a GUI that can be used to administer SQL Server.

SQL Server Management Studio allows you to create and manage your databases as well as administer your SQL Server configuration.

SSMS used to be included with SQL Server installations, but is now made available as a separate download/installation.

Tasks you can perform with SSMS include:

  • Create, modify & delete databases and database objects such as tables, views, stored procedures, etc
  • Query your databases
  • Create and maintain user accounts and roles
  • Create and maintain backups (either manual or scheduled)
  • Import and export data from/to other databases
  • Replicate databases across multiple machines

SSMS allows you to do most tasks either programatically or via the GUI. For example, you can create a database by right-clicking and selecting New Database, or you can run a SQL script to create it by clicking New Query.

The Object Explorer

The pane on the left of SSMS is the Object Explorer. The Object Explorer enables you to navigate through your databases, security settings, replication, management, and other server objects.

Screenshot of right-clicking on the Object Explorer in SQL Server 2014.
Right-clicking on an option in the Object Explorer usually brings up a contextual menu that enables you to choose an option.

Connecting to SQL Server

When you first open up SQL Server Management Studio, you will be prompted to connect to SQL Server.

Screenshot of the Connect to Server prompt in SQL Server 2014
The “Connect to Server” screen appears when you open SSMS.

You could have many instances of SQL Server on your network so you can choose which instance to connect to.

You can connect to multiple databases across multiple servers across any network that you’re connected to. All of these will show up under the Object Explorer on the left pane. So you can easily toggle between a database on your local machine and a database on the other side of the world. You can also transfer data between them, run distributed queries across both databases, and more.