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.

2. Write a script that (1) creates a login ID named “RobertHalliday” with the password “HelloBob”; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named “RobertHalliday” for the login; and (4) 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 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.

4. Using the Management Studio, create a login ID named “RBrautigan” with the password “RBra9999,” and set the default database to the MyGuitarShop database. Then, grant the login ID access to the MyGuitarShop database, create a user for the login ID named “RBrautigan”, and assign the user to the OrderEntry role you created in exercise 1. Note: If you get an error that says “The MUST_CHANGE option is not supported”, you can deselect the “Enforce password policy” option for the login ID.

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 RobertHalliday that you created in exercise 2, and (4) grants all standard privileges except for REFERENCES and ALTER to RobertHalliday for the Admin schema.

Answer 1

CREATE DATABASE MyGuitarShop ;/* Creates a new database called MyGuitarShop */

USE MyGuitarShop ;/* To use MyGuitarshop as the current database */

CREATE ROLE OrderEntry ;/* Creates a new role called OrderEntry */

GRANT INSERT ON Orders ,OrderItems TO OrderEntry ;/* Grant Insert permission to the Role "OrderEntry " for the tables Orders ,OrderItems */

GRANT UPDATE ON Orders ,OrderItems TO OrderEntry ;/* Grant update permission to the Role "OrderEntry " for the tables Orders ,OrderItems */

GRANT SELECT ON Orders ,OrderItems TO ALL;/* Gives select permission on to all user tables*/