Lesson 3: Create a role and grant it to UserA (SQL)

Create a role that contains the privileges necessary to debug a procedure and correct any errors found during the debugging process, then grant that role to UserA. Make sure that UserA is granted administrative rights for the role, so that he can grant the role to, or revoke the role from, other users as necessary.

Prerequisites

This lesson assumes that you have completed all previous lessons in this tutorial. See Lesson 2: Grant privileges to UserA (SQL).

Task

In Interactive SQL, execute the following statement to create the role:
CREATE ROLE DebugAndFix;

Execute the following statement to grant the required system privileges to the role:

GRANT ALTER ANY OBJECT, DEBUG ANY PROCEDURE TO DebugAndFix;

Execute the following statement to grant the new role to UserA, with administrative rights:
GRANT ROLE DebugAndFix TO UserA WITH ADMIN OPTION;

Results

The DebugAndFix role, which has the DEBUG ANY PROCEDURE and ALTER ANY OBJECT system privileges, has been created and granted to UserA, along with administrative rights.

Next

Proceed to Lesson 4: Convert UserA to a user-extended role and grant that role to UserB (SQL).

Lesson 3: Create a role and grant it to UserA (SQL)

Task

See also