Create SQL Server user with SSMS and set up access rights

How to create a new SQL Server user with SQL Server Management Studio (SSMS)?

This SQL Server administration tutorial explains how to create a SQL Server user login with SSMS, used to connect to the databases of an instance. The user login can have a Windows security or the SQL Server security. Security management in SQL Server in general and in SQL databases is a task for database administrators. They are called Data Base Administrators (DBA).

In this short SSMS tutorial you will learn how to create a new SQL Server user login without any script. It is also possible to create a login with a SQL script.

1. Open the SSMS menu to create a SQL Server user

First step, open SQL Server Management Studio. A database is visible and accessible from the SSMS interface. In our case, the database is named DB-Expert-Only. To create a new user account, follow these steps:

  1. From Security
  2. Right-click on the Logins sub-folder.
  3. Now click on New Login.
Create a new SQL Server user login with SSMS
Create a SQL Server user with SSMS

2. Configure the SQL Server user with SSMS

In the Login – New window enter the credentials, in this case it is US-Expert-Only. Then choose SQL Server authentication and enter the password twice. Then uncheck Enforce password expiration so that the password never expires. This rule should be set up according to your security needs.

Enter the new user name and SQL Server password in SSMS
Enter the new login name and SQL Server password in SSMS

3. Add roles to the SQL Server login

Now, in the Server Roles tab check the sysadmin button, note that the account created will then have full rights to the SQL Server database instance. That is, the new login will have full privileges on this test database.

Assign the sysadmin role on the SQL Server database
Assign the sysadmin role on the SQL Server database

4. Configure the user login mapping to databases

To assign the created account to the database, click on User Mapping, all existing databases are displayed. Clicking on a database displays the list of users in the database as below. Click on the master database, check db_owner and public, then validate. Note that the db_owner role is an administrator role with full privileges on these databases.

Check db_owner for the SQL Server login
Check db_owner for the SQL Server user login

5. Finish the creation of the SQL Server user

All the necessary configurations are done, in the Securables tab, simply click on OK. This is the last step in creating a new SQL Server user with SSMS.

Finish the user creation in SSMS by the validation
Finish the user creation in SSMS by the validation

The new US-Expert-Only user login now appears in the SQL instance login list. The system lists the new credentials after creating a SQL Server login.

Create a SQL Server user with SSMS and check the New login in the Logins list
Create a SQL Server user with SSMS and check the New login in the Logins list

Then, to test the access, log in with the new login as an authorized user. Simply click on File then Connect, and enter the login and password, then click on Connect again.

Finally, this tutorial explains how to create a SQL Server user login, step by step from the SSMS GUI. Many other functions are available to check the existence of an object in the scripts generated by SSMS.

Create a SQL Server database with SSMS

MS SQL database administration tutorials

Leave a Comment

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