Creating and Managing Users in SQL Server

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 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 details.

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

Creating and Managing Users in SQL Server

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 to see all the users of the server?

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

Creating and Managing Users in SQL Server

How to create a new user?

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

Creating and Managing Users in SQL Server

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

Login Name: testuser

Select the SQL Server Authentication and provide the Password and Confirm Password

Check and Uncheck the Enforce Password Policy and Enforce Password Expiration.

Then select the default Database and default Language as shown in the below image,

Creating and Managing Users in SQL Server

Server Roles:

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

Creating and Managing Users in SQL Server

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.

Creating and Managing Users in SQL Server

Status Tab:

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

Creating and Managing Users in SQL Server

That’s it. We have a 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.

Creating and Managing Users in SQL Server

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.

Creating and Managing Users in SQL Server

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

Creating and Managing Users in SQL Server

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 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.

SUMMARY

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 need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *