In previous post, we learnt basic introduction to SQL Server. In this post we will learn about SSMS (SQL Server Management Studio) software and how to use it. SSMS is a client tool which is used to connect and interact with SQL Server. Before working with SSMS tool, we should be aware that SQL Server is server software. In client server model, client sends request to server and the server software responds to the client request. We use SSMS as a client tool to interact with SQL server. Interaction is for the purpose of management of SQL Server. By using SSMS, we can control different features such as user management, security management, querying different database objects such as system tables, user defined tables, executing stored procedures etc.
Multiple instances of SSMS client tool can be used to interact with an instance of SQL server. If you have installed SSMS at your PC, you can run multiple instances of SSMS. Each instance of SSMS will have its separate session. In real-world production environment, SSMS client tools will be distributed across different client machines and they can interact with an instance of the SQL server simultaneously.
Connection with SQL server
To interact with SQL server, there must be connection between SSMS and SQL Server. We will learn how we can connect SSMS with SQL server. Needless to say that to connect SSMS with the SQL server, SSMS must be pre-installed on our system. Download SSMS from Microsoft site and install it at your system from this link if you have not installed it.
First of all, check that an instance of SQL Server running on system because SSMS can connect with SQL Server only if it is running. If instance is running, open SSMS (SQL Server Management Studio); otherwise start an instance of SQL Server on the system. You can check the instance of SQL Server running at your system by opening the Services.
On running the SSMS, "Connect to Server" dialog box will open up as shown below. You have to fill the correct information to different text box controls to authenticate with SQL Server. Only authenticated user is allowed to login SQL Server because the server is a secure system.
Server Type: SQL Server Database Engine
Server name: Enter the server name that you want to connect with. Server name is an IP address or URL. At your local system, local host can be used as server name. Depending upon the instance of SQL Server running on system, you can decide about server name. In production environment, SQL Server will be installed at a server machine. At your local system, server name can be local host or MSSQLLocalDB etc.
LocalDB: At my system, an instance of SQL Server Express LocalDB is running. By default, access to the instance of LocalDB is limited to its owner. According to MSDN, LocalDB is a feature you select during SQL Server Express installation, and is available when you download the media. If you download the media, either choose Express Advanced or the LocalDB package. Visual Studio 2019 and 2022 customers should install SQL Server Express 2019.
The SqlLocalDB.exe can be searched in C:\Program Files\Microsoft SQL Server folder. At my system, it is available at C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe
Authentication: SQL Server is a secure system which needs correct credentials to connect with. There are different modes to authenticate to SQL Server.
- SQL Server Authentication: In this mode, enter valid username and password to connect with SQL server instance. This is known as SQL Server authentication mode to connect with.
- Windows Authentication: You can also use windows authentication mode to connect with SQL server. Connecting SSMS with SQL server using windows authentication is known as trusted connection. The best thing about windows authentication is that user doesn’t have to worry about username and password to connect with SQL Server. If you are using your personal computer then you don't have to worry about username and password, automatically the system will connect with SQL server in the trusted connection mode but in the business environment where active directory is used, the admin has to create different groups and users and so admin has to do some twitching to allow windows authentication to work for a user.
- Mixed Authentication: There is a third option to connect with SQL Server which is known as mixed mode. In this mode you can use either SQL server authentication or Windows authentication.
I have used windows authentication to login.
After login, the first step is to explore all objects which are available in SQL Server. SSMS provides Object Explorer window to look at any object of the server. Press F8 to view Object Explorer if it is not visible. In SSMS, Object Explorer window looks like below snapshot.
Note that if SQL Server is freshly installed then only system databases and system database objects will be visible in Object Explorer.
Session in SSMS
When SSMS connects with SQL Server, session is created for the login. When user closes SSMS then session terminates for the login.
Running SQL Script in SSMS
Running SQL script is SSMS is fundamental skill. Press CTRL + N to open a SQL Query editor window. You can also click New Query button in Menu Bar for this. In the editor, write test script SELECT * FROM INFORMATION_SCHEMA.TABLES and press F5 or CTRL+E to execute the script. You can use Execute button to run the script. Before running a script, relevant database must be selected from dropdown. In this example, master database is selected.
Execution Plan of Query
The next skill in SSMS is to get the execution plan of a query. Press CTRL + L to get it. Look at the following result in snapshot for the above query.
Query Options
Query options can be set using by clicking ‘Query Options’ button in Menu Bar or by using Query > Query Options. Look at the following screenshot.
On clicking Query Options, we get Query Options dialog box which can be used to set the result or set ANSI settings etc.
Creating projects in SSMS
- To create a new project, press CTRL+SHIFT+N.
- View all projects in Solution Explorer.
A SQL Server script project has connections and queries apart from many other objects. You can create multiple script files in a project. A snapshot is given below.
Settings various options e.g. Line Number
- Go to Tools > Options > Text Editor > All Languages > General
- You can wrap script and display line numbers in query editor
Customize dialog box
The Tools > Customize dialog box can be used to customize items on tool bar. We can add or remove a command.
User vs. Login in SQL Server
A user is associated with a database. User is provided some or all privileges to work with the database and its objects. On the other hand, login is associated with login to SQL Server. A login is created to login to SQL Server.
How to create user in database
You can create a user in a database inside Security > Users subfolder. User can be created in GUI mode or by using CREATE USER statement.
There are five ways to create a user in database which are as follows.
- A user can be created with login.
- A user can be created without login.
- A login can be created by mapping with a certificate.
- A login can be created by mapping with an asymmetric key.
- A login can be created by using Windows credentials.
Right click Security > Users subfolder to create a user in GUI mode in SSMS and click ‘New’ menu item. Database User dialog box opens. Look at the Database User dialog box in following snapshot.
Where is authentication details stored?
When an instance of the SQL Server is installed, login is created with username and password which is used to authenticate user when next time user logins to the server. By default, the username is SA. This username and password is stored in master database in system tables. The master database is a system database which you can look in Object Explorer. Press F8 to open Object Explorer in SSMS. To query logins you must login as admin. There are different approaches to query all login lists. We will learn it later. When queried, we find that password is in the encrypted format. You get the extra decimal representation of the password. Password is encrypted using encryption algorithm and cannot be decrypted unless you know the algorithm.
How to create, alter and drop login in database
There are different ways to create, modify and delete login in SQL Server.
- By using SSMS GUI Mode
- By executing system stored procedure
You can create login by using sp_AddLogin system store procedure. There are different parameters associated with this store procedure. The 1st parameter is username, 2nd one is password, 3rd one is database name, 4th is language, 5th is security identifier and 6th one is encryption option. Only first two parameters are mandatory, others are optional.
EXEC sp_AddLogin appliedk, appliedk
On executing the above command in SSMS, we get login access using SQL Server authentication. Look at the following snapshot to login using the above username and password.
The snapshot is given below when user logins to SQL Server.
How to change password: If you want to change the password then you can use system store procedure named as sp_password. This procedure asks for two parameters- old password and new password. The third parameter is login name which is optional. If an old password is not known then you can use null for that.
There are two additional system store procedures to manage the logins. They are sp_helplogins and sp_droplogins. The sp_helplogins provides login details which are created on the server.
EXEC sp_helplogins
The result is as follows.
In SQL server you can create multiple databases. You must have relevant authorization to create a database. We will learn about SQL Server System databases in next post.
No comments:
Post a Comment