GRANT and REVOKE in MSSQL

In Microsoft SQL Server (MSSQL), GRANT and REVOKE commands are also used for managing permissions on database objects.

 GRANT:

The GRANT command is used to give specific permissions to users or roles.

Syntax:

GRANT permission_name 

    ON object_name

    TO {user_name | role_name | "PUBLIC"};

Example:

GRANT SELECT, INSERT ON dbo.employees TO user1;

This grants the SELECT and INSERT permissions on the employees table in the dbo schema to the user user1.

GRANT SELECT ON dbo.employees TO HR;

This grants the SELECT permission on the employees table to the HR role.

 REVOKE:

The REVOKE command is used to take back permissions that have been granted from users or roles.

Syntax:

REVOKE permission_name 

    ON object_name

    FROM {user_name | role_name | "PUBLIC"};

Example:

REVOKE SELECT, INSERT ON dbo.employees FROM user1;

This revokes the SELECT and INSERT permissions on the employees table from the user user1.

REVOKE SELECT ON dbo.employees FROM HR;

This revokes the SELECT permission on the employees table from the HR role.

Just like in Oracle, it's essential to use these commands judiciously to maintain the security and integrity of your MSSQL database.