Chapter $17$

How to manage database security

Exercises

$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

tables. 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 new 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. For each administrator:

Create a login ID that consists of the administrator$$s first and last name with no space between.

Set a password of $$TempPa$$$1$ for the new login ID$.$

Set the default database for the new login ID to MyGuitarShop.

Create a user for the login ID with the same name as the login ID$.$

Assign the user to the OrderEntry role you created in exercise $1.$

$($Hint: Begin the script by declaring two variables, @DynamicSQL and @LoginName.$)$

$4.$ Using Management Studio, create a login ID named $$RBrautigan$$ with the password $$RBra$99999,$

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 does the following:

Creates a schema named Admin.

Transfers the table named Addresses from the dbo schema to the Admin schema.

Assigns the Admin schema as the default schema for the user named RobertHalliday that you created

in exercise $2.$

Grants all standard privileges except for REFERENCES and ALTER to RobertHalliday for the

Admin schema.