CREATE USER

Add a user to the current database.

Syntax
      CREATE USER user 
         [{ FOR | FROM } LOGIN login_name] [WITH DEFAULT_SCHEMA = schema]

      CREATE USER user 
         [{ FOR | FROM } CERTIFICATE certificate] [WITH DEFAULT_SCHEMA = schema]

      CREATE USER user 
         [{ FOR | FROM } ASYMMETRIC KEY asym_key] [WITH DEFAULT_SCHEMA = schema]

      CREATE USER user WITHOUT LOGIN


Key
   user          Name for the user in this database, up to 128 characters.

   login_name    A valid SQL Server login. When this SQL Server login enters the
                 database it will acquire the name and ID of user.
                 By default user will be mapped to the SQL Server login with the same name.

   certificate   The certificate for which the database user is being created.

   asym_key      The asymmetric key for which the database user is being created.

   schema        The first schema that will used to resolve object names for user.
                 default schema = dbo

   WITHOUT LOGIN Don't map user to an existing login.

All members of the sysadmin fixed server role have a default schema of dbo.

A user that is not mapped to a SQL Server login may connect to other databases as guest, this just requires GRANT CONNECT TO guest;

Examples

CREATE LOGIN user86
    WITH PASSWORD = 'pa$$word';
USE MyDatabase;
CREATE USER user86 FOR LOGIN user86;
GO

CREATE LOGIN user87
    WITH PASSWORD = 'pa$$word';
USE MyDatabase;
CREATE USER user87 FOR LOGIN user87
    WITH DEFAULT_SCHEMA = Marketing;
GO

"My friend, there are two kinds of people in the world, Those with guns and those who dig." - The Good, The Bad, and The Ugly

Related commands:

CREATE LOGIN
ALTER USER
DROP USER

Equivalent Oracle command:

CREATE USER