SQL Server CREATE USER

Summary: in this tutorial, you’ll learn how to use the SQL Server CREATE USER statement to add a user to the current database.

Introduction to the SQL Server CREATE USER statement #

The SQL Server CREATE USER statement allows you to add a user to the current database. The following shows the basic syntax of the CREATE USER statement:

CREATE USER username 
FOR LOGIN login_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the user after the CREATE USER keywords.
• Second, specify the login_name for the user. The login name must be valid on the server. To create a login, you use the CREATE LOGIN statement.

SQL Server CREATE USER statement #

First, create a new login called alex with the password 'Uvxs245!':

CREATE LOGIN alex
WITH PASSWORD='Uvxs245!';Code language: SQL (Structured Query Language) (sql)

Second, switch the current database to the BikeStores:

USE BikeStores;Code language: SQL (Structured Query Language) (sql)

Third, create a user with the username alex that uses the alex login:

CREATE USER alex
FOR LOGIN alex;Code language: SQL (Structured Query Language) (sql)

If you use SSMS, you can see the user alex under BikeStores > Security > Users list as shown in the following picture:

The user alex can connect to SQL Server using the alex login’s password and accesses the BikeStores database. However, the user alex cannot access any tables and other database objects in the BikeStores database.

To do that, you need to add the user alex to the database roles or grant it permissions.

Summary #

• Use the CREATE USER statement to add a user to the current database.