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:

• One user is mapped to exactly one login.
• Two users in the same database cannot map to the same login.
• A login can have a user in each database.
• A login which user is dbo is the owner of the database.

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.

• with rcli, you can specify a database to connect to with the option -d or -database, e.g. rcli -U=john -P=xxxxxx -database=sales myscript.sql.
• you can also set a default database for the login john, with the option DEFAULT_DATABASE of CREATE LOGIN or ALTER LOGIN.

Examples

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

CREATE USER jp FOR LOGIN jean_pierre;