How to Setup and Use Microsoft SQL Server Management Studio

What is SSMS?

SQL Server Management Studio (SSMS) is a free Windows application to configure, manage, and administer Microsoft SQL Server (MSSQL). SSMS includes an Object Explorer to view and interact with databases and other elements, a Query window to write and execute Transact-SQL queries, and script editors for developers and administrators.

Installing SSMS

Step 1: Let’s get started by installing SSMS. The first step is to download the latest general availability (GA) version of SSMS. The General availability version is the most stable version for everyone to use.

When downloading, choosing Save As will allow us to choose the location to save the installer file for easy access. Let’s save it to the Desktop.

Step 2: Next, from our desktop, we double-click to open the SSMS Setup file to begin the SSMS install process.

Step 3: Installing SSMS is a very simple and straightforward process. When the installer opens, we need to click Install to accept the license and start installing components.

The installation will take several minutes and may require a restart of your computer if it needs to update older components. Once setup is completed and all specified components are installed successfully, you can close the installer and remove it from your system.

[OPTIONAL]: If you need to install MSSQL, you may find this KB article helpful.

To install MSSQL or run SSMS from the server where it is installed, you will need to use Remote Desktop to log in to the server. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected:

How to Use a Remote Desktop
Windows: Accessing Your Server with Remote Desktop
Using Remote Desktop Protocol (RDP) to Log into Your Windows Server

 

Using SSMS to Manage MSSQL

The settings we use when connecting to MSSQL with SSMS depend on whether we are accessing it on a remote system/network or locally.

• When accessing MSSQL locally we can refer to server name as localhost.
• On remote systems, the most reliable way to reference the server is by the IP address.

 

Step 1: Let’s jump right in by opening SSMS. To open SSMS, click the new icon on your desktop

or, click on the Start Menu and start typing SSMS or look for the program in the Microsoft SQL Server Tools folder. For convenience, you may choose to right-click the program and pin it to the Start menu and/or pin it to the taskbar, so it is easier to find.

Step 2: When we start SSMS, it asks us to connect to an MSSQL server and allows us to set connection properties. Below are available options and what we need to supply to get connected.

If you are having difficulties connecting to MSSQL on a remote system, you or the SQL Administrator may need to configure MSSQL to allow TCP/IP Ports and configure a hardware firewall or the Windows Firewall to allow access from your IP address. This KB article covers Windows Firewall Basics.

If you experience Error 18456 when attempting to connect to MSSQL, this KB article can help you identify and resolve the issue: Troubleshooting Error 18456, login failed for user

Step 3: Once we have entered the required information, we can click Connect to access to the SQL Server.

 

SSMS Object Explorer

The SSMS Object Explorer is the main area we will interact with to Manage MSSQL. It includes options for managing databases, security and more.

At the top of Object Explorer, we have a Connect drop-down that allows us to make more connections, and icons to connect/disconnect the current MSSQL server object. The example below shows we are connected to an MSSQL server on IP 192.168.11.128 on the default port 1433, as the user RemoteUser.

Right-Clicking Server Objects in Object Explorer allows you to access menus for additional tasks related to the object type.

Expanding Objects in Object Explorer allows you to see the contents, and interact and manage them. To expand an object, you can double-click it, or click on the [+] symbol to the left of the object.

 

Managing Databases in SSMS Object Explorer

Let’s create a new database…expand Databases to see the current list of databases. Right-click Databases and choose New Database…

The New Database page opens. This is where we name the database, select the owner and set options including the MSSQL compatibility level. Once we are done, we click OK to create the database with the options we have selected.

 

Managing Security in SSMS Object Explorer

Let’s create a Login…expand Security to see the available objects. Expand Logins to see the current list of logins. Right-click Logins and choose New Login…

The New Login page opens. This is where we name login (1), set the authentication type, set a password, select a role, and map the user to a database. In most cases, we want to create SQL logins, so we choose SQL Authentication (2). It is a good idea to uncheck the box for User must change password at next login (3) to avoid the potential for remote logins to fail with Error 18456. Set the Server Roles (4) to allow the login to have any required server-wide privileges. User Mapping allows us to map the login to databases and select the user and default schema. Once we are done configuring the login, click OK (5) to create the login with the options we have selected.

Let’s allow a login for a user and change permissions. In SSMS Object Explorer, expand Security, Logins. A red x on a Login indicates login is disabled.

To fix this, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.

We can fix the issue where a user’s login is locked out, and grant login permission to connect to the database engine. Object Explorer, Security allows us to change properties of logins, reset passwords, rename the login, and delete the login.

In this article, we learned that SSMS is a powerful and convenient way to manage an MSSQL Server. SSMS is free, it can be installed with only a few steps on a Windows system, and it will allow you to connect to and manage MSSQL Server directly from the server or a remote workstation.

For more information, please call, chat or open a ticket with our support heroes at support@liquidweb.com.