Mastering Transact-SQL Authorization

Question 1

Who can execute statements or perform operations on an entity?

Answer 1

Only authorized users are able to execute statements or perform operations on an entity.

Question 2

What happens if an unauthorized user attempts to execute a Transact-SQL statement?

Answer 2

The execution of the Transact-SQL statement or the operation on the database object will be rejected.

Question 3

What are the three Transact-SQL statements related to authorization?

Answer 3

The three Transact-SQL statements are: GRANT, DENY, and REVOKE.

Question 4

What is the syntax for the GRANT statement?

Answer 4

GRANT {ALL [PRIVILEGES]} | permission_list [ON [class::] securable] TO principal_list [WITH GRANT OPTION] [AS principal]

Question 5

Provide an example of a GRANT statement.

Answer 5

GRANT CREATE TABLE, CREATE PROCEDURE TO peter, mary

Example of granting multiple permissions.

Question 6

What does the WITH GRANT OPTION do in a GRANT statement?

Answer 6

It gives user B the additional capability of granting the privilege to other users.

Question 7

What does the DENY statement do?

Answer 7

The DENY statement prevents users from performing actions and removes existing permissions.

Question 8

What is the syntax for the DENY statement?

Answer 8

DENY {ALL [PRIVILEGES]} | permission_list [ON [class::] securable] TO principal_list [CASCADE] [AS principal]

Question 9

Provide an example of a DENY statement.

Answer 9

DENY CREATE TABLE, CREATE PROCEDURE TO peter

Example of denying multiple permissions.

Question 10

What does the REVOKE statement do?

Answer 10

The REVOKE statement removes one or more previously granted or denied permissions.

Question 11

What is the syntax for the REVOKE statement?

Answer 11

REVOKE [GRANT OPTION FOR] { [ALL [PRIVILEGES]] | permission_list } [ON [class::] securable] FROM principal_list [CASCADE] [AS principal]