Kailash's Blogs

Create User In SQL Server Management Studio

Kailash Chandra Behera | Wednesday, April 12, 2023
Kailash's Blogs

Introduction

Like system users, users can be created also in the SQL servers. Each individual who needs access to SQL Server databases can be given a user account and permissions. Here in this blog (Create User In SQL Server Management Studio), we will demonstrate to create user in SQL for a new user to log in.

Getting Started

A login is the identity of the person or process that is connecting to an instance of SQL Server. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can also create a login that isn't based on a Windows principal such as an SQL Server login. To use SQL Server Authentication, the Database Engine must use mixed-mode authentication.

Here we will see how to create users for the login SQL server with basic features. To create login SQL Server provides two ways one is using SQL Server Management Studio, and another is using Transact-SQL.

We will go with the first option here as the main motive of this blog but before going to SQL Server create new user steps, ensure the administrator has selected server properties with securities authentication.

Create User In SQL Server Management Studio
Create User In SQL Server Management Studio

Steps Of SQL Server Create User For Database


  1. In Object Explorer, expand the folder of the server instance in which you want to create the new login.
  2. Right-click the Security folder, point to New, and select Login...., the following window will appear

  3. Create User In SQL Server Management Studio

  4. In the Login - New dialog box, on the General page, enter the name of a user in the Login name box.
  5. To create a login that is saved on a SQL Server database, select SQL Server authentication.
    1. In the Password box, enter a password for the new user. Enter that password again into the Confirm Password box.
    2. When changing an existing password, select Specify old password, and then type the old password in the Old password box.
    3. To enforce password policy options for complexity and enforcement, select Enforce password policy. For more information, see Password Policy. This is a default option when SQL Server authentication is selected.
    4. To enforce password policy options for expiration, select Enforce password expiration. Enforce password policy must be selected to enable this checkbox. This is a default option when SQL Server authentication is selected.
    5. To force the user to create a new password after the first time the login is used, select User must change password at next login. Enforce password expiration must be selected to enable this checkbox. This is a default option when SQL Server authentication is selected.
  6. Then click the Server Roles Page, the Server Roles page lists all possible roles that can be assigned to the new login. The server role is used to grant server-wise security privileges to users, Select the required server roles. Here we will go with the public role.
  7. Create User In SQL Server Management Studio>
  8. Select the User Mapping Page. Then Select the databases that this login can access. When you select a database, its valid database roles are displayed in the Database role membership for: database_name pane. Then click on the OK button
  9. Create User In SQL Server Management Studio
  10. Again, go to object explorer and right click on the SQL Server name-> click on the property. On the server property window, Select the Permissions-> Select to grant this permission to the login -> Click on the OK button.
    Create User In SQL Server Management Studio

Server Role Options:

he following options are available for server role.

  1. bulkadmin check box Members of the bulkadmin fixed server role can run the BULK INSERT statement.
  2. dbcreator check box Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
  3. diskadmin check box Members of the diskadmin fixed server role can manage disk files.
  4. processadmin check box Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.
  5. public check box All SQL Server users, groups, and roles belong to the public fixed server role by default.
  6. securityadmin check box Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
  7. serveradmin check box Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
  8. setupadmin check box Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.
  9. sysadmin check box Members of the sysadmin fixed server role can perform any activity in the Database Engine.

Summary

In the above of this blog Create User In SQL Server Management Studio, we learned to create sql server authentication login. I hope the above helped you create a new user in SQL Server.

Related Articles

  1. Create A Database in SQL Server
  2. Recover SQL Databases from Suspect Mode in SQL Server
  3. SQL Sever Firewall Ports
  4. SQL Server Shrink Log File
  5. Shrink Database (SQL Server)

Thanks


No comments: