A Windows user account or a SQL Server login allows a user to log in to the system. A user who subsequently wants to access a particular database of the system also needs a database user account to work with the database. Therefore, users must have a database user account for each database they want to use. The database user account can be mapped from the existing Windows user accounts, Windows groups (of which the user is a member), logins, or roles.
To manage database security, you can use
- SQL Server Management Studio
- Transact-SQL statements
The following subsections describes both ways to manage database security.
Managing Database Security Using Management Studio
To add users to a database using SQL Server Management Studio, expand the server, expand the Databases folder, expand the database, and expand Security. Right-click Users and click New User. In the Database User dialog box (see Figure 12-3), enter a username and choose a corresponding login name. Optionally, you can choose a default schema for this user.

Managing Database Security Using Transact-SQL Statements
The CREATE USER statement adds a user to the current database. The syntax of this statement is user_name is
CREATE USER user_name
[FOR {LOGIN login |CERTIFICATE cert_name |ASYMMETRIC KEY key_name}]
[ WITH DEFAULT_SCHEMA = schema_name ]
Code language: CSS (css)
user_name is the name that is used to identify the user inside the database. login specifies the login for which the user is being created. cert_name and key_name specify the corresponding certificate and asymmetric key, respectively. Finally, the WITH DEFAULT SCHEMA option specifies the first schema that will be searched by the server when it resolves the names of objects for this database user.
Example 12.7 demonstrates the use of the CREATE USER statement.
USE sample;
CREATE USER peter FOR LOGIN [NTB11901\pete];
CREATE USER mary FOR LOGIN mary WITH DEFAULT_SCHEMA =
my_schema;
Code language: PHP (php)
The first CREATE USER statement creates the database user called peter for the Windows login called pete. pete will use dbo as its default schema because the DEFAULT SCHEMA option is omitted. (Default schemas will be described in the section “Default Database Schemas” later in this chapter.)
The second CREATE USER statement creates a new database user with the name mary. This user has my_schema as her default schema. (The DEFAULT_SCHEMA option can be set to a schema that does not currently exist in the database.)
The ALTER USER statement modifies a database username, changes its default schema, or remaps a user to another login. Similar to the CREATE USER statement, it is possible to assign a default schema to a user before the creation of the schema.
The DROP USER statement removes a user from the current database. Users that own securables (that is, database objects) cannot be dropped from the database.
Default Database Schemas
Each database within the system has the following default database schemas:
- guest
- dbo
- INFORMATION_SCHEMA
- sys
The Database Engine allows users without user accounts to access a database using the guest schema. (After creation, each database contains this schema.) You can apply permissions to the guest schema in the same way as you apply them to any other schema. Also, you can drop and add the guest schema from any database except the master and tempdb system databases.
Each database object belongs to one and only one schema, which is the default schema for that object. The default schema can be defined explicitly or implicitly. If the default schema isn’t defined explicitly during the creation of an object, that object belongs to the dbo schema. Also, the login that is the owner of a database always has the special username dbo when using the database it owns.
The INFORMATION_SCHEMA schema contains all information schema views. The sys schema, as you may have already guessed, contains system objects, such as catalog views.