How to create a new user using SQL statements

How to create a new user using SQL statements
Transact-SQL reference
 
Sp_adduser
Add a security account to a new user in the current database. This process is included for backward compatibility. Use sp_grantdbaccess.
Syntax
Sp_adduser [@ loginame =] 'login'
[, [@ Name_in_db =] 'user']
[, [@ Kgname =] 'group']
Parameters
[@ Loginame =] 'login'
The logon name of the user. The data type of login is sysname, with no default value. Login must be an existing Microsoft SQL Server logon or Microsoft Windows NT user.
[@ Name_in_db =] 'user'
The name of the new user. The user data type is sysname, and its default value is null. If no user is specified, the user name is the login name by default. The specified user is the name of the logon ID of the new user in the database, which is different from that of the SQL Server.
[@ Kgname =] 'group'
A group or role. A new user automatically becomes a member of a group or role. The group data type is sysname, and the default value is null. The Group must be a valid group or role in the current database. Microsoft SQL Server 7.0 uses roles rather than groups.
Return code value
0 (successful) or 1 (failed)
Note
The SQL Server user name can contain 1 to 128 characters, including letters, symbols, and numbers. However, the user name cannot:
Contains the backslash (/).
Is null, or is a null string ('').
After adding a user, you can use the grant, deny, and revoke statements to define permissions. These permissions control user activities.
Use sp_helplogin to display a list of valid login names.
Use sp_helprole to display a list of valid role names. When a role is specified, the user automatically obtains the permissions defined for the role. If no role is specified, the user is granted the permission of the default public role. To add a user to a role, you must provide the username value (username can be the same as login_id ).
To access the database, you must use sp_adduser or sp_grantdbaccess to grant access to the login, or the database must have a guest security account.
Sp_adduser cannot be executed in a user-defined transaction.
Permission
Only DBO and members of the SysAdmin fixed server role can execute sp_adduser.
Example
A. Add a user
The following example uses the existing logon Victoria to add the user Victoria to the existing fort_mudge role in the current database.
Exec sp_adduser 'victoria ', 'victoria', 'fort _ Mudge'
B. Add a user name (using the same logon ID)
The following example shows how to add the default username Margaret to the current database. The username belongs to the default public role.
Exec sp_adduser 'Margaret'
C. Add users (use different user names)
The following example adds the haroldq logon to the current database and uses the Harold user name, which belongs to the fort_mudge role.
Exec sp_adduser 'haroldq ', 'harold', 'fort _ Mudge'
Sp_adduser [@ loginame =] 'login'
[, [@ Name_in_db =] 'user']
[, [@ Kgname =] 'group']