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.

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.

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.

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.

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.

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.

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

• Create a SQL Server database with SSMS
• Install a second instance of SQL Server
• Clear the SQL Server transaction log and fix 9002 error