Creating and Managing Users in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Creating and Managing Users in SQL Server

In this article, I am going to discuss Creating and Managing Users in SQL Server step by step. In this article, we are just going to see how to create a new user and how to reset the password of an existing user. In our upcoming articles, we will discuss these things in detail.

Let’s first log in to the SQL Server using the Run as administrator mode using SSMS (SQL Server Management Studio) as shown in the below image.

Select the Server Type as Database Engine, provide the Server name and select the Authentication as Windows Authentication and then click on the connect button which will connect to the SQL Server Database.

How do we see all the users of the server?

To see all the users of the server navigate to the Security->Logins folder on the left side of your window as shown below.

How to create a new user in SQL server?

To create a new user, Right-click on the Logins folder and click on the New Login option as shown in the below image

Once you click on the New Login option it will open a new popup as shown below.

General Tab:

Select the General Tab and provide the below details

1. Login Name: testuser
2. Select the SQL Server Authentication and provide the Password and Confirm Password
3. Check and Uncheck the Enforce Password Policy and Enforce Password Expiration.
4. Then select the default Database and default Language as shown in the below image,

Server Roles:

Select the Server Roles Tab and then check the public and sysadmin check box as shown in the below image.

User Mapping:

Then select the user mapping tab and check the databases to which the above user can access as shown below along with all the system databases such as tempdb, master, model, and msdb.

Status Tab:

From the status tab, select the Grant and Enabled option and then click on the OK button as shown below.

That’s it. We have created the testuser and you can see the testuser under the Security => Logins folder. Let’s log in to the server using the testuser as shown in the below image.

Here we need to select the Authentication type as SQL Server Authentication, provide the Login Name as testuser and the password, and then click on the connect button which will connect to the database successfully.

How to Reset the Password of an existing user?

As we know by default the sa user is created when we installed the database. If you forgot the password for sa user or any other user then how you can reset the password.

To reset the password of a particular user, Right-click on that user and click on the properties tab as shown in the below image which will open the properties for that user where you can change the password.

Once you click on the properties option, it will open the Login Properties windows for that user as shown below.

Here you need to reset the password and once you reset the password then click on the OK button. So in this article, we discussed how to create a new user and how to change the password of an existing user in the SQL server. Here we create a new user testuser and we change the password of sa user.

We are going to use these two users in our next article, where we are going to discuss the Logon Trigger in SQL Server with examples. Here, in this article, I try to explain the Creating and Managing Users in SQL Server step by step. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.