SQL User – Definition of user permissions at the database level

In SQL Server administration, there are two terms that can easily be confused: SQL Login and SQL User. In a previous article, we took a closer look at SQL Login, and here we will focus on SQL User.

SQL User

While SQL Login is used to log in to SQL Server and defines permissions at the server level, SQL User is defined at the database level and is used to specify permissions at the level of individual databases or individual objects within the database – such as permissions for reading, writing, viewing code, executing procedures, handling sql tables, and more.

A SQL User must first have a Login created. This is logical because you use a login to connect to SQL Server, and without it, you cannot access the server at all. Once you have a login created, you can work with it and map the login to individual databases. In the Server Security – Logins section, you click on the respective login, and in the User Mapping section, you assign access to the login for individual databases. You can then choose from the default roles to assign to the login for each database. After confirming, a User is automatically created. You can check this in the Security – Users section for the respective database. One login can be mapped to multiple users.

Database Roles That Can Be Assigned to Users

1. db_owner – The most powerful database role, can do anything in the database.
2. db_securityadmin – Manages roles and permissions within the database.
3. db_accessadmin – Allows a user to add or remove other users, NT groups.
4. db_backupoperator – Allows performing backups and restores from backups.
5. db_ddladmin – Permissions that allow users to perform Data Definition Language (DDL) operations within the database. DDL includes CREATE, DROP, ALTER, TRUNCATE.
6. db_datareader – Common permission for non-IT users, allows viewing data in any table at the database level.
7. db_datawriter – Permission to insert, delete, or modify table contents.
8. db_denydatareader – Prevents a user from reading tables.
9. db_denydatawriter – Prevents a user from inserting, deleting, or modifying table contents.

In addition to the default database roles, it is, of course, possible to create custom roles.

SQL Script for Creating a User (Create User)

CREATE USER User FOR LOGIN [Login];

EXEC sp_addrolemember ‘db_datareader’, ‘User’;