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 optionDEFAULT_DATABASE
ofCREATE LOGIN
orALTER LOGIN
.
Examples
CREATE USER john;
CREATE USER john FOR LOGIN john; -- same as above
CREATE USER jp FOR LOGIN jean_pierre;