1. Write a script that creates a user-defined database role named OrderEntry in

Question

1.      Write a script that creates a user-defined database role named OrderEntry in the MyGuitarShop database. Give INSERT and UPDATE permission to the new role for the Orders and OrderItems table. Give SELECT permission for all user tables.

Write a script that uses dynamic SQL and a cursor to loop through each row of the Administrators table and (1) create a login ID for each row in that consists of the administrator’s first and last name with no space between; (2) set a temporary password of “temp” for each login; (3) set the default database for the login to the MyGuitarShop database; (4) create a user for the login with the same name as the login; and (4) assign the user to the OrderEntry role you created in exercise 1.

Explanation / Answer

I have desgined and developed the user-defined database role named OrderEntry in the MyGuitarShop database. I have added the comments for each part of code and attached the final output of it.

Let me explain you in brief and in step-by-step manner:-

Step-1:

The initial part is to define the user-defined database role named "OrderEntry" which is based on the MyGuitarShop database by implementing the Insert and Update permissions to the new role.

Example:-

USE MyGuitarShop
CREATE ROLE OrderEntry

Step-2:

The next part is to create a Login ID for each number of rows which consists of admin's first and last name, set a temporary password for each login, and by setting up a default database which is "MyGuitarShop".

Example:-

USE MyGuitarShop
CREATE LOGIN Naheed WITH PASSWORD = 'nNahe007',
DEFAULT_DATABASE = MyGuitarShop

Step-3:

The final step is to assign the user with the OrderEntry role which is used to create within the user.

Example:-

CREATE USER Naheed FOR LOGIN Naheed

Script- 1:

USE MyGuitarShop
CREATE ROLE OrderEntry
GRANT UPDATE
ON Orders
TO OrderEntry
GRANT INSERT,UPDATE
ON OrderItems
TO OrderEntry

Script- 2:

CREATE TABLE OrderItems

(LOGIN varchar(128))

INSERT OrderItems

VALUES ('Biscuits')

INSERT OrderItems

VALUES ('Cake')

INSERT OrderItems

VALUES ('Chocolate')

INSERT OrderItems

VALUES ('Juices')

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.