Thursday , November 14 2024

GRANT, REVOKE and DENY Commands in SQL Server(TSQL)

You can use GRANT, REVOKE, and DENY commands on many database objects in SQL Server.

With the GRANT command, you can authorize a user.

With the REVOKE command, you can revoke a given authorization.

With the DENY command, you can deny an authorization. In this article we will examine where we can use these commands.

With GRANT, REVOKE and DENY, you can perform many levels of authorization, canceling privileges, and denying an authorization. In this article, we will examine what we can do at the database, schema and table level.

GRANT at the database level:

With the GRANT command, we give the following permissions to the TestLogin on the TestDB database at the database level.

with “WITH Grant Option“, we can grant a login to authorize other logins. Can be used in the following ways. I recommend that all authorization be performed by DBAs.

REVOKE at the database level:

With the help of the REVOKE command, we can revoke the privileges we gave at the database level with GRANT. The use is as follows.

DENY at the database level:

With the DENY command, we can deny the above privileges at the database level for a user. The deny command is usually used to prevent some of the high-level privileges from being granted at a low-level. Its use is as follows.

GRANT at Schema level:

On a schema, we can use the grant command to grant the following privileges to a user.

Revoke at schema level:

With the Revoke command, we can revoke the privileges granted at the schema level. Its use is as follows.

DENY at schema level:

With the DENY command, we can deny the above privileges at the schema level for a user. The deny command is usually used to prevent some of the high-level privileges from being granted at a low-level. Its use is as follows.

GRANT at table level:

We can use the GRANT command on a table to give the following privileges to a user.

Revoke at table level:

With the Revoke command, we can revoke the privileges granted at the table level. Its use is as follows.

DENY at table level:

With the DENY command, we can deny the above privileges at the table level for a user. The deny command is usually used to prevent some of the high-level privileges from being granted at a low-level. Its use is as follows.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *