CREATE USER (TSQL Statement)

creates a new user in the current database, so that a login can get access to this database.

Syntax

CREATE USER user_name
[
    { FOR | FROM } LOGIN login_name
]

Arguments

user_name
is the user name.
login_name
is the name of the login on behalf of which the user is created. If omitted, it is the same name as user_name.
login_name cannot be sa (only user dbo can have sa as login).

Remarks

Only sa or dbo is allowed to execute this statement.

To access a database, a login must have a user in this database.

The following observations apply:

The command SHOW U displays the list of users (and corresponding login) and roles.

If connection error occurs

If you get an error like login "john" has no user in database "trashdb" when connecting, it is because you try to login as john without specifying a database accessible to john.

Examples

CREATE USER john;
CREATE USER john FOR LOGIN john;  -- same as above

CREATE USER jp FOR LOGIN jean_pierre;
RSQL, a simple alternative to Microsoft SQL Server