Reference no: EM131067003

1. Write a script that creates a user-defined database role named PaymentEntry in the AP database. Give UPDATE permission to the new role for the Invoices table, UPDATE and INSERT permission for the InvoiceLineItems table, and SELECT permission for all user tables.

2. Write a script that (1) creates a login ID named "AAaron" with the password "aaar9999"; (2) sets the default database for the login to the AP database; (3) creates a user named "Aaron" for the login; and (4) assigns the user to the PaymentEntry role you created in exercise 1.

3. Write a script that creates four login IDs based on the contents of a new table called NewLogins:

CREATE TABLE NewLogias
(LoginName varchar(128));
INSERT NewLogins
VALUES (113Brown,), ('CChaplin,), ('DDyer'), ('EEbbers');

Use dynamic SQL and a cursor to perform four actions for each row in this table: (1) create a login with a temporary password based on the first four letters of the login name followed by "9999"; (2) set the default database to the AP database; (3) create a user for the login with the same name as the login; and (4) assign the user to the PaymentEntry role you created in exercise 1.

4. Using the Management Studio, create a login ID named "FFalk" with the password "ffa19999," and set the default database to the AP database. Then, grant the login ID access to the AP database, create a user for the login ID named "FFalk", and assign the user to the PaymentEntry 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 PaymentEntry. (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 ContactUpdates from the dbo schema to the Admin schema, (3) assigns the Admin schema as the default schema for the user named Aaron that you created in exercise 2, and (4) grants all standard privileges except for REFERENCES and ALTER to AAaron for the Admin schema.

Attachment:- CreateMyGuitarShop.rar