Securely managing user access is a critical aspect of database administration. In SQL, you can create a new user with login privileges using the `CREATE LOGIN`, `CREATE USER`, and `GRANT` statements. Let's delve into these commands and understand how to use them for creating a user with login access.
The `CREATE LOGIN` statement is used to create a login for a specified user. Meanwhile, the `CREATE USER` statement associates an existing login with a user defined in the current database. The `GRANT` statement, on the other hand, is used to provide permissions to a user or role.
Creating a Login
The `CREATE LOGIN` statement is used to create a new login for a SQL Server instance. The login can be associated with a database user using the `CREATE USER` statement.
Here is the syntax of the `CREATE LOGIN` statement: ```sql CREATE LOGIN LoginName WITH PASSWORD='Password' ; ``` Replace `LoginName` with the name you want for your user and `Password` with the password you want the user to use.
Login Properties
The `WITH` clause can include several properties for the login. These include `PASSWORD`, `CHECK_EXPIRATION`, `CHECK_POLICY`, `DEFAULT_DATABASE`, `ENCRYPTION`, and `SID`. Let's explore a few.
For instance, `CHECK_EXPIRATION` clause is used to indicate whether the password expiry check is enabled for this login. It can be disabled by using `CHECK_EXPIRATION = OFF`.
Creating a User
The `CREATE USER` statement is used to create a user in a specific database. The login created with `CREATE LOGIN` will be associated with this user.
Here is the syntax of the `CREATE USER` statement: ```sql CREATE USER UserName FOR LOGIN LoginName ; ``` Replace `UserName` with the name you want for your user in the database and `LoginName` with the login you created previously.
Granting Permissions
Once the user has been created, you can grant permissions to that user using the `GRANT` statement. This statement gives the user the ability to perform certain actions in the database.
Here is the syntax of the `GRANT` statement: ```sql GRANT column_name ON table_name TO user_name ; ``` Replace `column_name` with the name of the column the user should have access to, `table_name` with the name of the table, and `user_name` with the name of your user.
Granting Database-Level Permissions
To grant database-level permissions, use the `GRANT` statement followed by the relevant permission, database name, and `TO` keyword. For example, to grant the SELECT permission on all tables to the salesuser: ```sql GRANT SELECT ON sales Articles TO salesuser; ```