ITEC 4242: Module 4 Assignment Solutions Spring 2019 - Database Tasks

This assignment solution addresses the ITEC 4242 Module 4 requirements, focusing on database security and authentication within SQL Server 2017. The solution begins with screenshots of the Object Explorer Details view and discusses the advantages of Windows Authentication and Active Directory Authentication methods. It then covers creating a new database and tables, including screenshots of the tables folder and results of a SELECT query on the Books table. Furthermore, the solution creates charts illustrating table access and privilege levels for different roles, such as Promotions, Author, and Customer Service Representative. Screenshots of the Securables page within each role's Properties window are provided, demonstrating the assigned permissions. Finally, screenshots of each user's Membership page confirm the appropriate role assignments. The assignment incorporates screenshots to illustrate the steps taken and the outcomes achieved, and it cites relevant sources to support the analysis and findings. The assignment covers database security, authentication, and SQL Server tasks.

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
Module 4 Assignment Solutions:
Task1 - open the Object Explorer Details view (F7), make sure all information is visible, and take a
screenshot.
P a g e 1 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
Task 2 - discuss the advantages of Windows Authentication and Active Directory Authentication
methods
Windows authentication is more secure since it utilizes a certificate based security system.
Windows-validated logins pass an entrance token rather than a name and secret phrase to SQL
Server. Windows relegate the entrance token when the client signs in. It contains an interesting
security ID (SID) for that client and the SIDs of any groups to which the client has a place. It
compares with all the SIDs in the sys. server_principals and login is either allowed or denied access
to SQL Server. When the client interfaces with the database, the client is not required to enter a
client name and secret key. A single sign-on at login gives access to all administrations that help
Windows verification ("Windows Authentication", 2019)
SQL Server Authentication implies the record lives in the SQL server ace database yet no place on
the Domain. The username and secret phrase gets stored in the ace database. On the off chance
that the secret key needs transformation, it is changed on each case separately to keep them all in a
state of harmony. Utilizing SQL Server Authentication enables you to supersede the Domain Security
approaches concerning secret word intricacy and secret word lapse (Hunter, Allen & Allen, 2009).
Task 3 – (1) in the object Explorer window, expand the Tables folder in your new database to show
your created tables then take a screenshot and (2) perform a SELECT query on the Books table and
screenshot the results.
P a g e 2 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
Task 4 - For each role, create a chart that shows each table that they would need access to and the
privilege levels of access needed (SELECT, INSERT, UPDATE, DELETE).
P a g e 3 | 13
Marketing
Manager
Promotions
Orders
Customers
Books
ALL
SELECT
SELECT
SELECT

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
P a g e 4 | 13
Data Entry
Clerk
Author
Publisher
Books
Book Author
ALL
ALL
ALL
ALL
Customer
Service
Represent
ative
Customers
Order Items
Orders
SELECT
ALL
ALL

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
Task 5 - take a screenshot (3 total) of the Securables page within each role’s Properties window, showing
the permissions assigned to one of the requisite tables.
P a g e 5 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
P a g e 6 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
P a g e 7 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
Task 6 - take a screenshot (5 total) of each user’s Membership page, showing they have been assigned
the appropriate role.
P a g e 8 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
P a g e 9 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
P a g e 10 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
P a g e 11 | 13

ITEC 4242 Module 4 Assignment Spring 2019, Session 2
References
1. Guidorizzi, R. (2013). Security: Active Authentication. IT Professional, 15(4), pp.4-7.
2. Hunter, L., Allen, R., & Allen, R. (2009). Active directory cookbook. Sebastopol, CA:
O'Reilly.
3. Windows Authentication. (2019). Retrieved from
https://docs.microsoft.com/en-us/iis/configuration/system.webserver/security/
authentication/windowsauthentication/
4. Download SQL Server Management Studio (SSMS) - SQL Server. (2019). Retrieved from
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-
P a g e 12 | 13