Creating New User Login In SQL Server

Introduction

SQL Server allows for the creation of users and logins. Each individual who needs access to SQL Server databases can be given their user account and permissions.

The administrator can assign roles and schemas while creating a login account. This will depend on the kind of purposes and functionality access to be granted or denied for a new account.

Here, we will create a new login account for a new user to log in. Firstly, ensure the administrator has selected server Properties with Securities authentication before creating New Login Account.

Example

Now in SQL Server Management Studio (SSMS),

Step 1. Right-click on Security in Object Explorer and choose New Login.

Step 2. In the General option of the Login-New window: Provide the New User Login account Name and login authentication. Click OK (If the window is closed, select your new account name in the Security option of your Object explorer and double-click on it).

Step 3. In Server Roles Option: Server-wide security privileges. Select the lists of all possible schemas that the new database user can own.

By default, the login will be assigned to the public role. It means the object is available to all users.

Step 4. In User Mapping Option: In this, specify databases that the user account can access if the login needs more access in one or other databases. In the Membership, select the lists of all possible database membership roles the new database user can own. 

List of the roles that can be assigned to New/Existing Users

1. db_owner. Allowed to perform all configuration and maintenance activities on the database and can also drop the database.
2. db_securityadmin. Allowed to modify role membership and manage permissions. Adding principles to this role could enable unintended privilege escalation.
3. db_accessadmin. Allowed to add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
4. db_backupoperator. Allowed to go for backup of the database.
5. db_ddladmin. Allowed to run any Data Definition Language (DDL) command in a database.
6. db_datawriter. Allowed to add, delete, or change data in all user tables.
7. db_datareader. Allowed to read complete data from all user tables.
8. db_denydatawriter. Cannot add, modify, or delete any data in the user tables within a database.
9. db_denydatareader. Cannot read any data in the user tables within a database.

 

In Securables Option: There are options of Permissions, Grant, or deny in it.

 

In Status Option: Select the setting for Grant and enable connection to the database engine. Click OK.

Now open your SQL Server Management Studio and do Login in it with the New Login Account. When you enter the password, it will ask you for new password creation, fill it in and use the SQL server database with your account.

Now refresh your server and see your login account; you can change properties by selecting it.

Summary

This article taught us how to create a New user step by step in SQL Server.