Grant / Revoke permissions in SQL Server

You can grant, grant, delegate or revoke on many objects in the database of SQL Server. This article will show you how to decentralize and revoke permissions.

Decentralize on the table

Can assign permissions to users on the table with the rights granted can include SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER or ALL.

Syntax

 GRANT quyen ON doi_tuong TO nguoi_dung; 

Quyen

Permissions specified for the user. Maybe:

Right Description SELECT Ability to execute SELECT statement on INSERT table Ability to execute INSERT command on UPDATE table Ability to execute UPDATE command on DELETE table Ability to execute DELETE command on table REFERENCES Ability to create constraints refer to ALTER table The ability to execute the ALTER TABLE statement on the table to change the table definition. ALL ALL does not grant all rights on the table but grants rights under ANSI-92 standard, including SELECT, INSERT, UPDATE, DELETE and REFERENCES.

Subjects

The name of the database object you want to empower. In the case of empowerment on the table, it is the table name.

nguoi_dung

Usernames will be empowered.

For example

If you want to grant SELECT, INSERT, UPDATE and DELETE permissions on the table for users to be smithj, run the GRANT command below.

 G RANT SELECT, INSERT, UPDATE, DELETE ON nhanvien TO smithj; 

You can use the ALL keyword to indicate that you want to grant all rights under ANSI-92 to smithj users.

 GRANT ALL ON nhanvien TO smithj; 

If you only want to grant SELECT permission on the table for all users, give it to the public rights group (PUBLIC).

 GRANT SELECT ON nhanvien TO PUBLIC; 

See also: How to decentralize users in MS SQL Server

Revoke permissions on the board

After empowerment, you may want to revoke the given rights with the REVOKE command, withdraw the SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER or ALL permissions.

Syntax

 REVOKE quyen ON doi_tuong FROM nguoi_dung; 

Quyen

Rights will be revoked, possibly:

Right Description SELECT Ability to execute SELECT statement on INSERT table Ability to execute INSERT command on UPDATE table Ability to execute UPDATE command on DELETE table Ability to execute DELETE command on table REFERENCES Ability to create constraints refer to ALTER table The ability to execute the ALTER TABLE statement on the table to change the table definition. ALL ALL does not grant all rights on the table but grants rights under ANSI-92 standard, including SELECT, INSERT, UPDATE, DELETE and REFERENCES.

Subjects

The name of the database object you want to revoke permissions. In the case of empowerment on the table, it is the table name.

nguoi_dung

User name will be revoked.

For example

If you want to revoke the DELETE permission on the user account of the anderson user, run the REVOKE command below.

 REVOKE DELETE ON the user FROM anderson; 

If you want to revoke the rights under ANSI-92 on the user table for user and userj, use the ALL keyword, run the GRANT command below.

 REVOKE ALL ON the user FROM anderson; 

If you have given the SELECT permission to the public user group (all users) on the user table and want to revoke this permission, use the REVOKE command below.

 REVOKE SELECT ON the user FROM PUBLIC; 

Previous article: Index in SQL Server

The following article: CREATE LOGIN command in SQL Server