Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024

Creating Users on Microsoft SQL Server

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

The programming language SQL is the framework of Microsoft SQL Server, which is used to manage databases and query data. The table structure used by SQL Server maintains the data's security and consistency while allowing data and functions' connectivity. An account used to access a database in an MS SQL Server database is referred to as a user.

SQL Query Image

Source

This article will teach us how to create a new user on MS SQL Server using different methods.

Create a new Login in SQL Server

We must first create a new login based on Windows Authentication, SQL Server Authentication, a certificate, or an asymmetric key before we can create a database user.

Use the CREATE LOGIN statement to add a new login. It creates a login for a SQL Server instance. The specified user account will subsequently be associated with the login. 

The following is the syntax:

Window Authentication

CREATE LOGIN loginName   
   FROM WINDOWS
[ WITH DEFAULT_DATABASE = databaseName
| DEFAULT_LANGUAGE = languageName ];

 

SQL Server Authentication

CREATE LOGIN login_name 
  WITH PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , SID = sid_value
  | DEFAULT_DATABASE = database_name
  | DEFAULT_LANGUAGE = language_name
  | CHECK_EXPIRATION = { ON | OFF }
  | CHECK_POLICY = { ON | OFF }
  | CREDENTIAL = credential_name ];

 

Certificate

CREATE LOGIN login_name
FROM CERTIFICATE certificate_name;

 

Asymmetric Key

CREATE LOGIN login_name
FROM ASYMMETRIC KEY asym_key_name;
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Create Users on MS SQL Server

A user is created for a database in SQL Server, whereas a login is created for the server instance. A Login must be linked to a user to connect to a database. For any database, a login can only be mapped to one user. The database is the scope of a user.

A new user can be created using two different methods:

  1. Using T-SQL
  2. Using SSMS(SQL Server Management Studio)
  3. Using Window Authentication

Method 1: Using T-SQL

For the SQL server to add users to the database, you can use the T-SQL create user command. The syntax for the SQL create user command is as follows:

Create user <username> for login <loginname>

 

Example

Run the following query to generate the user name "TestUser" with the mapping to the login name "TestLogin" in the TestDB database.

create user TestUser for login TestLogin 

Method 2: SSMS(SQL Server Management Studio)

Before creating a user account, we should first create a login under any name. You can follow the steps mentioned above to create a new Login. Use the login name "TestLogin" for now.

  1. Connect the SQL Server and expand the databases folder. Expand the security folder and the database called "TestDB," where we will create the user account. To view the next screen, right-click Users and select the new user.

    Image of SQL Server Connection
    Source
     
  2. In the user name field, type "TestUser," and then click the ellipse to choose the login name "TestLogin."

    User Name Field
    Source
     
  3. To display the login name, click OK. To create the "TestUser" user, click OK once again.

    Displaying User Name
    Source
     

After following these steps, you can create users on Microsoft SQL Server.

Method 3: Window Authentication

The steps to creating a user using Window Authentication are as follows:

  1. Open Object explorer in the SQL Server Management Studio. 
     
  2. Click on Server_instance_nameSecurity Logins.
     
  3. Right-click on Logins and then select New Login.
     
  4. Type the name of a new user in the Login name field on the General page.
     
  5. Click on the Window Authentication. 
     
  6. Choose one of the following tasks, depending on the role and permissions you want to give this user:

    • Assign the new login ID to the sysadmin role on the Server Roles page.
    • Grant minimum permissions to the user if you do not want to assign the sysadmin role to the user.
       

Must Read SQL Clauses

Frequently Asked Questions

What is the user in SQL Server?

A database-level security principal is a user. Logins must be associated with a database user to connect to a database. A login can only be mapped as one user in each database, even though it can be mapped to several databases as distinct users. A user without a login can be created in a database that is only partially contained.

Which type of user is the default user in SQL Server?

The default database user access mode is MULTI_USER. Any user with the authorization to access the database can do so in this database user access mode.

Name the different types of user accounts.

The different types of user accounts are: 

  • System account
  • Superuser account
  • Ordinary user account
  • Guest user account

Conclusion

This article extensively discussed the topic of Creating users on a Microsoft SQL server in detail. We started with an introduction and debated the definition of an MS SQL server. Later, we also discussed the methods of creating users in SQL Server. 

We hope this blog has helped you enhance your knowledge of Creating users on Microsoft SQL Server. If you want to learn more, check out our other articles on the topics like Azure SQL ServerAzure Synapse SQLOperational Databases, and many more on our platform Coding Ninjas Studio.

Check out this problem - Smallest Distinct Window .

For peeps out there who want to learn more about Data Structures, Algorithms, Power programming languages, JavaScript, interview questions, or any other upskilling, please refer to our guided paths on Coding Ninjas Studio. Enroll in our courses, go for mock tests, solve available problems, and interview puzzles. Also, you can focus on interview stuff- interview experiences and an interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Happy Coding!

Topics covered
1.
Introduction
2.
Create a new Login in SQL Server
2.1.
Window Authentication
2.2.
SQL Server Authentication
2.3.
Certificate
2.4.
Asymmetric Key
3.
Create Users on MS SQL Server
3.1.
Method 1: Using T-SQL
3.1.1.
Example
3.2.
Method 2: SSMS(SQL Server Management Studio)
3.3.
Method 3: Window Authentication
4.
Frequently Asked Questions
4.1.
What is the user in SQL Server?
4.2.
Which type of user is the default user in SQL Server?
4.3.
Name the different types of user accounts.
5.
Conclusion