Create a Database User - SQL Server (2023)

  • Article
  • 6 minutes to read

Applies to: Create a Database User - SQL Server (1) SQL Server (all supported versions) Create a Database User - SQL Server (2) Azure SQL Database Create a Database User - SQL Server (3) Azure SQL Managed Instance Create a Database User - SQL Server (4) Azure Synapse Analytics Create a Database User - SQL Server (5) Analytics Platform System (PDW)

This topic describes how to create the most common types of database users. There are eleven types of users. The complete list is provided in the topic CREATE USER (Transact-SQL). All varieties of SQL Server support database users, but not necessarily all types of users.

You can create a database user by using SQL Server Management Studio or by using Transact-SQL.

Understanding the Types of Users

Management Studio presents 6 options when creating a database user. The following graphic shows the 6 options in the green box, and indicates what they represent.

Create a Database User - SQL Server (6)

Selecting the Type of User

Login or user that is not mapped to a login

If you are new to SQL Server, it can be difficult to determine what type of user you want to create. First ask yourself, does the person or group that needs to access the database have a login? Logins in the master database are common for the people who manage the SQL Server and for people who need to access many or all of the database on the instance of SQL Server. For this situation, you will create a SQL user with login. The database user is the identity of the login when it is connected to a database. The database user can use the same name as the login, but that is not required. This topic assumes that a login already exists in SQL Server. For information about how to create a login, see Create a Login

If the person or group that needs to access the database does not have a login and if they only need access to one or few databases, create a Windows user or a SQL user with password. Also called a contained database user, it is not associated with a login in the master database. This is an excellent choice when you want to be able to easily move your database between instances of SQL Server. To use this option on SQL Server 2016 (13.x), an administrator must first enable contained databases for the SQL Server, and the database be enabled for containment. For more information, see Contained Database Users - Making Your Database Portable.

Important

When connecting as a contained database user you must provide the name of the database as part of the connection string. To specify the database in Management Studio, in the Connect to dialog box, click Options, and then click the Connection Properties tab.

(Video) How to Create Login , Create User and Assign Permission to user in SQL SERVER

Select SQL user with password or a SQL user with login based on a SQL Server authentication login, when the person connecting cannot authenticate with Windows. This is common when people outside of your organization (for example customers) are connecting to your SQL Server.

Tip

For people inside your organization, Windows authentication is a better choice, because they won't have to remember an additional password, and because Windows authentication offers additional security features such as Kerberos.

Background

A user is a database level security principal. Logins must be mapped to a database user to connect to a database. A login can be mapped to different databases as different users but can only be mapped as one user in each database. In a partially contained database, a user can be created that does not have a login. For more information about contained database users, see CREATE USER (Transact-SQL). If the guest user in a database is enabled, a login that is not mapped to a database user can enter the database as the guest user.

Important

The guest user is ordinarily disabled. Do not enable the guest user unless it is necessary.

As a security principal, permissions can be granted to users. The scope of a user is the database. To connect to a specific database on the instance of SQL Server, a login must be mapped to a database user. Permissions inside the database are granted and denied to the database user, not the login.

(Video) SQL Server DBA Tutorial 55- How to Create a Database User in SQL Server

Permissions

Requires ALTER ANY USER permission on the database.

Create a user with SSMS

  1. In Object Explorer, expand the Databases folder.

  2. Expand the database in which to create the new database user.

  3. Right-click the Security folder, point to New, and select User....

  4. In the Database User - New dialog box, on the General page, select one of the following user types from the User type list:

    • SQL user with login

    • SQL user with password

    • SQL user without login

    • User mapped to a certificate

    • User mapped to an asymmetric key

    • Windows user

      (Video) MS SQL Server 2019 | How to create Database, SQL Login and User

  5. When you select an option, the remaining options in the dialog may change. Some options only apply to specific types of database users. Some options can be left blank and will use a default value.

    User name
    Enter a name for the new user. If you have chosen Windows user from the User type list, you can also click the ellipsis (...) to open the Select User or Group dialog box.

    Login name
    Enter the login for the user. Alternately, click the ellipsis (...) to open the Select Login dialog box. Login name is available if you select either SQL user with login or Windows user from the User type list.

    Password and Confirm password
    Enter a password for users who authenticate at the database.

    Default language
    Enter the default language of the user.

    Default schema
    Enter the schema that will own objects created by this user. Alternately, click the ellipsis (...) to open the Select Schema dialog box. Default schema is available if you select either SQL user with login, SQL user without login, or Windows user from the User type list.

    Certificate name
    Enter the certificate to be used for the database user. Alternately, click the ellipsis (...) to open the Select Certificate dialog box. Certificate name is available if you select User mapped to a certificate from the User type list.

    Asymmetric key name
    Enter the key to be used for the database user. Alternately, click the ellipsis (...) to open the Select Asymmetric Key dialog box. Asymmetric key name is available if you select User mapped to an asymmetric key from the User type list.

  6. Select OK.

Additional Options

The Database User - New dialog box also offers options on four additional pages: Owned Schemas, Membership, Securables, and Extended Properties.

  • The Owned Schemas page lists all possible schemas that can be owned by the new database user. To add schemas to or remove them from a database user, under Schemas owned by this user, select or clear the check boxes next to the schemas.

    (Video) SQL Server DBA Tutorial 69-How to Create Contained Database User in SQL Server

  • The Membership page lists all possible database membership roles that can be owned by the new database user. To add roles to or remove them from a database user, under Database role membership, select or clear the check boxes next to the roles.

  • The Securables page lists all possible securables and the permissions on those securables that can be granted to the login.

  • The Extended properties page allows you to add custom properties to database users. The following options are available on this page.

    Database
    Displays the name of the selected database. This field is read-only.

    Collation
    Displays the collation used for the selected database. This field is read-only.

    Properties
    View or specify the extended properties for the object. Each extended property consists of a name/value pair of metadata associated with the object.

    Ellipsis (...)
    Click the ellipsis (...) after Value to open the Value for Extended Property dialog box. Type or view the value of the extended property in this larger location. For more information, see Value for Extended Property Dialog Box.

    Delete
    Removes the selected extended property.

Create a user using T-SQL

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    (Video) How to Create MS SQL Database Using SQL Server Management Studio - For Beginners

    -- Creates the login AbolrousHazem with password '340$Uuxwp7Mcxo7Khy'. CREATE LOGIN AbolrousHazem WITH PASSWORD = '340$Uuxwp7Mcxo7Khy'; GO -- Creates a database user for the login created above. CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem; GO 

For more information, see CREATE USER (Transact-SQL) which contains many more Transact-SQL examples.

See Also

Principals (Database Engine)
Create a Login
CREATE LOGIN (Transact-SQL)

FAQs

How do you create a database user? ›

Expand the database in which to create the new database user. Right-click the Security folder, point to New, and select User.... In the Database User - New dialog box, on the General page, select one of the following user types from the User type list: SQL user with login.

What is the command to create a user in SQL? ›

Steps
  1. Log in to SQL *Plus: sqlplus '/ as sysdba'
  2. Create a new user with an administrator password: create user user_name identified by admin_password ; ...
  3. Assign the sysdba privilege to the new Oracle user: grant sysdba to user_name ;

How to create SA Login in SQL Server? ›

More Information
  1. Start SQL Server. ...
  2. Expand server_name, and then expand Security. ...
  3. Right-click Logins, and then click New Login.
  4. Enter a user name for SQL Server. ...
  5. Enter a password. ...
  6. Select the Microsoft Forecaster database. ...
  7. Grant access to the Microsoft Forecaster database. ...
  8. Click OK.

How can create user defined database role in SQL Server? ›

In Object Explorer, expand the server where you want to create the new server role. Expand the Security folder. Right-click the Server Roles folder and select New Server Role.... In the New Server Role -server_role_name dialog box, on the General page, enter a name for the new server role in the Server role name box.

How can I create my own user? ›

Create a local user or administrator account in Windows
  1. Select Start > Settings > Accounts and then select Family & other users. ...
  2. Next to Add other user, select Add account.
  3. Select I don't have this person's sign-in information, and on the next page, select Add a user without a Microsoft account.

What is a database user? ›

Database users are granted access to read, insert, update, and delete specific objects that define a set of fields and business rules. These objects can also update one or more database tables. In the Users application, you use the Database Access action to create database users.

What is the command to create user? ›

GOTO CMD(Command Prompt) Run as Administrator. Type command “ net user username /add ” or “ net user username password /add ” New user with the specified username will be added.

What command is used to create users? ›

The useradd command creates a new user account. The login parameter must be a unique string (its length is can be configured by administrators using the chdev command). You cannot use the ALL or default keywords in the user name. The useradd command does not create password information for a user.

What is the syntax to create user? ›

CREATE USER username IDENTIFIED BY password [DEFAULT TABLESPACE tablespace_name] [TEMPORARY TABLESPACE temp_ts_name] [QUOTA UNLIMITED|size ON tablespace_name] [PROFILE user_profile] [PASSWORD EXPIRE] [ACCOUNT LOCK|UNLOCK]; Note: Attributes in brackets are optional and are assigned default values when omitted.

What is the difference between login and user in SQL Server? ›

A login provides to a user or application the ability to connect to a SQL Server instance, whereas a database user provides the login rights to access a database. Each database a login needs access to will require a database user to be defined, except when a login has been given sysadmin rights.

What is sa user in SQL Server? ›

The sa login, short for system administrator, is one of the riskiest server-level principals in SQL Server. It's automatically added as a member of the sysadmin fixed server role and, as such, has all permissions on that instance and can perform any activity.

How do I create a login authentication? ›

Using HTTP Basic Authentication
  1. A client requests access to a protected resource.
  2. The Web server returns a dialog box that requests the user name and password.
  3. The client submits the user name and password to the server.
  4. The server validates the credentials and, if successful, returns the requested resource.

How can create and assign user roles in the database? ›

  1. Creating and Assigning a Role – First, the (Database Administrator)DBA must create the role. ...
  2. Syntax – CREATE ROLE manager; Role created. ...
  3. Grant privileges to a role – ...
  4. Grant a role to users. ...
  5. Revoke privilege from a Role : REVOKE create table FROM manager;
  6. Drop a Role : DROP ROLE manager;
  7. Explanation –
Sep 27, 2018

Which role allows users to create users within a database? ›

To add and remove users to a database role, use the ADD MEMBER and DROP MEMBER options of the ALTER ROLE statement.

What are user roles in SQL Server? ›

SQL Server roles lets you group user logins together and manage server-level permissions. They play a central part in SQL Server security. SQL Server has two types of roles: Fixed server roles, which are built into SQL Server, and do not allow you to modify permissions or user-defined roles.

How do you create a user account manually? ›

My computer is in a workgroup
  1. To open User Accounts, click the Start button. ...
  2. Click Manage another account. ...
  3. Click Create a new account.
  4. Type the name you want to give the user account, click an account type, and then click Create Account.

What are the 3 types of user profiles for user accounts? ›

Types of User Profiles
  • Local User Profiles. A local user profile is created the first time that a user logs on to a computer. ...
  • Roaming User Profiles. A roaming user profile is a copy of the local profile that is copied to, and stored on, a server share. ...
  • Mandatory User Profiles. ...
  • Temporary User Profiles.
Apr 30, 2018

How do I create multiple users? ›

Add people to a home PC
  1. Select Start > Settings > Accounts > Family & other users.
  2. Under Other users > Add other user, select Add account.
  3. Enter that person's Microsoft account information and follow the prompts.

What are the 4 types of users? ›

Four basic types of Users. (and how to identify them)
  • Socializers:
  • Achievers:
  • Explorers:
  • Players:
Feb 23, 2021

What are different types of database users? ›

We can categorize them into seven categories as follows:
  • Database Administrators (DBA)
  • Database Designers.
  • System Analysts.
  • Application Programmers.
  • Naive Users / Parametric Users.
  • Sophisticated Users.
  • Casual Users / Temporary Users.
Dec 12, 2022

What is -- user command? ›

users command in Linux system is used to show the user names of users currently logged in to the current host. It will display who is currently logged in according to FILE. If the FILE is not specified, use /var/run/utmp. /var/log/wtmp as FILE is common.

Is create user a DDL command? ›

User Management

Use the following DDL commands to manage users in the system: CREATE USER. ALTER USER. DROP USER.

Which SQL command is used by user? ›

Grant command is used to give users permission for a particular action. Permission can be given for any data object. Grant command is a part of Data Controlling Language (DCL). Permissions can also be propagated using the "with grant" option clause from one user to another.

What is create user command in MySQL? ›

The CREATE USER statement creates new MySQL accounts. It enables authentication, role, SSL/TLS, resource-limit, password-management, comment, and attribute properties to be established for new accounts. It also controls whether accounts are initially locked or unlocked.

How can you create a user without useradd command? ›

Follow these steps to create a user without using useradd command in Red Hat Linux. # passwd user Changing password for user user. New password: Retype new password: passwd: all authentication tokens updated successfully. You should see [user@test ~]$ instead of -bash-4.1$ prompt.

Which statement creates a new user? ›

The CREATE USER statement creates a new user account in a database.

Can we create user without login in SQL Server? ›

The same Management Studio dialog used to create "normal" users can be used to create a user without login.

What is difference between schema and user in SQL Server? ›

In a SQL database, a schema is a list of logical structures of data. A database user owns the schema, which has the same name as the database manager. As of SQL Server 2005, a schema is an individual entity (container of objects) distinct from the user who constructs the object.

How to get current user in SQL Server? ›

CURRENT_USER() Function in SQL Server

This function in SQL Server is used to return the current user's name in the database of SQL Server in use. Features : This function is used to find the current user's name. This function comes under Advanced Functions.

How to create a user in MySQL? ›

To create a new user account in MySQL, follow these steps:
  1. Access command line and enter MySQL server: mysql.
  2. The script will return this result, which verifies that you are accessing a MySQL server. mysql>
  3. Then, execute the following command: CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
Dec 21, 2022

How do I manually create a user in Active Directory? ›

  1. Open the Server Manager, go to the Tools menu and select Active Directory Users and Computers.
  2. Expand the domain and click Users.
  3. Right-click on the right pane and press New > User.
  4. When the New Object-User box displays enter a First name, Last name, User logon name, and click Next.
  5. Enter a password and press Next.
Jan 18, 2023

Videos

1. Microsoft SQL Server 70-462 Tutorial | Creating Database Users
(O'Reilly - Video Training)
2. SQL Server - How to Create a Database User | FoxLearn
(Fox Learn)
3. Quick Tutorial - Users and Permissions in SQL Server
(Keil Jones)
4. How to Create User Account in SQL Server Tutorial
(OnlineITtuts Tutorials)
5. SQL Server DBA Tutorial 57- How to Create User Define Schema in a Database in SQL Server
(TechBrothersIT)
6. Adding Users To SQL Server
(Michael Schore)
Top Articles
Latest Posts
Article information

Author: Trent Wehner

Last Updated: 12/31/2022

Views: 5803

Rating: 4.6 / 5 (76 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Trent Wehner

Birthday: 1993-03-14

Address: 872 Kevin Squares, New Codyville, AK 01785-0416

Phone: +18698800304764

Job: Senior Farming Developer

Hobby: Paintball, Calligraphy, Hunting, Flying disc, Lapidary, Rafting, Inline skating

Introduction: My name is Trent Wehner, I am a talented, brainy, zealous, light, funny, gleaming, attractive person who loves writing and wants to share my knowledge and understanding with you.