A user account or a 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 user accounts, 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 describe both ways to manage database security.
1. Managing Database Security Using SQL Server Management Studio
To add users to a database using 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, enter a username and choose a corresponding login name. Optionally, you can choose a default schema for this user.
2. Managing Database Security Using Transact-SQL Statements
The CREATE USER statement adds a user to the current database. The syntax of this statement is
CREATE USER user_name
[FOR {LOGIN login |CERTIFICATE cert_name |ASYMMETRIC KEY key_name}]
[ WITH DEFAULT_SCHEMA = schema_name ]
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.12 demonstrates the use of the CREATE USER statement.
Example 12.12
USE sample;
CREATE USER peter FOR LOGIN [NTB11901\pete];
CREATE USER mary FOR LOGIN mary WITH DEFAULT_SCHEMA=my_schema;
NOTE To execute the first statement successfully, create the user account named pete and change the server (domain) name.
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. The second CREATE USER statement creates a new database user with the name mary. This user has my_schema as her default schema.
NOTE Each database has its own specific users. Therefore, the CREATE USER statement must be executed once for each database where a user account should exist. Also, a login can have only a single corresponding database user for a given 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.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.