by
A SQL user is an individual person or group that has a login that identifies them to a specific database. Typically, a SQL user has access to multiple databases and is able to do many things. Using the CREATE USER statement, you can create a new user and grant privileges on the database objects they own.
The CREATE USER statement can be used to create a user based on Windows Authentication, SQL Server Authentication, a certificate or an asymmetric key. The CREATE USER statement also lets you specify a default schema for the user.
You can create a user for the Login name that appears in the Security option of Object Explorer. This is useful if the user you are creating already has a Login in one or more databases, but you need to map the user to a different database.
When you create a user based on a login, the corresponding database is added to the SQL Server instance and the user is mapped to the new login. If a user has permission to access only a single database, you can use the DROP USER statement instead of the CREATE USER statement to remove the user from all databases.
Users are a key component in database administration because they allow you to define what actions a user can perform. These actions can include editing, deleting, updating, and resetting permissions on database objects.
Managing database users is important because it allows you to assign specific permissions to a single user, to a group of users, or to a set of roles. It also helps you manage user accounts and keep your database environment secure and protected.
Assigning a database user to a schema requires the use of T-SQL code and dbForge Studio for SQL Server. dbForge Studio for SQL Server has a built-in security manager that simplifies the process of granting or revoking permissions on a database object.
Step 1 – Connect to your SQL Server, expand the Databases folder from the Object Explorer and identify the database for which you need to create a user. Then, copy and paste the T-SQL command below into the Object Explorer query window.
The following example uses the CREATE LOGIN command to create a new Login account called HRDBLogin and the password HRDBLogin. It also uses the CREATE USER command to create a new user called Guru99.
In this example, we created a user account for the HRDBLogin Login in EDU_TSQL database and gave the user the SELECT, INSERT, UPDATE, DELETE, REFERENCES, and ALTER permissions on the Course table.
You can view the list of permissions granted to the user by selecting it in dbForge Studio for SQL Server and switching to the Securables option from the left. Once you have checked the list, you can also check whether the user has been granted any additional permissions by clicking on the Permissions tab in Security Manager.
