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.

Paste screen prints of your SQL script and its results below

2. Write a script that:

Creates a login ID named JohnThomkins with the password RogerWilson

Sets the default database for the login to the MyGuitarShop database

Creates a user named JohnThompkins for the login

Assigns the user to the OrderEntry role you created in Exercise 1.

3. Write a script that:

Uses dynamic SQL and a cursor to loop through each row of the Administrators table

Create a login ID for each row in that consists of the administrators first and last name with no space between

Set a temporary password of temp for each login

Set the default database for the login to the MyGuitarShop database

Create a user for the login with the same name as the login

Assign the user to the OrderEntry role you created in Exercise 1.

Using the Management Studio:

Create a login ID named MChanning with the password MC9999,

Set the default database to the MyGuitarShop database

Grant the login ID access to the MyGuitarShop database

Create a user for the login ID named MChanning

Assign the user to the OrderEntry role you created in Exercise 1.

5 Write a script that removes the user-defined database role named OrderEntry. (Hint: This script should begin by removing all users from this role.)

6 Write a script that (1) creates a schema named Admin, (2) transfers the table named Addresses from the dbo schema to the Admin schema, (3) assigns the Admin schema as the default schema for the user named JohnThomkins that you created in exercise 2, and (4) grants all standard privileges except for REFERENCES and ALTER to RobertHalliday for the Admin schema.