G3RJs5Su

1-- chapter 17 in class
2
3/*1.	Write a script that creates a user-defined database role named PaymentEntry 
4in the AP database. Give UPDATE permission to the new role for the Invoices table, 
5UPDATE and INSERT permission for the InvoiceLineItems table, and SELECT permission 
6for all user tables.
7*/
8
9ALTER ROLE PaymentEntry DROP MEMBER Aaaron;
10
11
12DROP ROLE IF EXISTS PaymentEntry;
13CREATE ROLE PaymentEntry;
14GRANT SELECT TO PaymentEntry;
15GRANT UPDATE ON Invoices TO PaymentEntry;
16GRANT INSERT, UPDATE ON InvoiceLineItems TO PaymentEntry;
17
18
19
20
21/*2.	Write a script that (1) creates a login ID named “AAaron” with the 
22password “aaar9999”; (2) sets the default database for the login to the AP database; 
23(3) creates a user named “AAaron” for the login; and (4) assigns the user to the PaymentEntry 
24role you created in exercise 1.
25*/
26
27
28IF EXISTS 
29    (SELECT name  
30     FROM master.sys.server_principals
31     WHERE name = 'AAaron')
32	 DROP login AAaron;
33
34DROP USER IF EXISTS AAaron;
35CREATE LOGIN AAaron WITH PASSWORD = 'aaar9999';
36CREATE USER AAaron;
37ALTER ROLE PaymentEntry ADD MEMBER Aaaron;
38
39
40
41
42
43/*3. Write a script that (1) creates a schema named Admin, (2) transfers the table named 
44ContactUpdates from the dbo schema to the Admin schema, (3) assigns the Admin schema as the 
45default schema for the user named AAaron that you created in exercise 2, and (4) grants SELECT, UPDATE, 
46INSERT, DELETE, EXECUTE to AAaron for the Admin schema.
47*/
48
49USE AP;
50GO
51
52IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.tables
53	WHERE TABLE_NAME = 'ContactUpdates' and TABLE_SCHEMA = 'dbo')
54		ALTER SCHEMA dbo TRANSFER Admin.ContactUpdates;
55
56DROP SCHEMA IF EXISTS Admin;
57GO
58
59CREATE SCHEMA Admin
60GO
61
62ALTER SCHEMA Admin TRANSFER dbo.ContactUpdates;
63
64ALTER USER AAaron WITH DEFAULT_SCHEMA = Admin;
65
66GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE ON SCHEMA :: Admin TO AAaron;