USE MICROSOFT SQL SERVER 22Murach's SQL Server 2016 for Developers: My Guitar Shop exercises (modified)Chapter 17How to manage database securityExercises1 Write a script that creates a user-defined database role named OrderEntry in theMyGuitarShop database. Give INSERT and UPDATE permission to the new role forthe Orders and OrderItems table. Give SELECT permission for all user tables.2 Write a script that (1) creates a login ID named "BillyHalliday" with the password"HelloBilly"; (2) sets the default database for the login to the MyGuitarShopdatabase; (3) creates a user named "BHalliday" for the login; and (4) assigns the userto 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 theAdministrators table and (1) create a login ID for each row that consists of theadministrator's last and first name with no space between; (2) set a temporarypassword of "dump" for each login; (3) set the default database for the login to theMyGuitarShop database; (4) create a user for the login with the same name as thelogin; and (4) assign the user to the OrderEntry role you created in exercise 1.4 Using the Management Studio, create a login ID named "ASmithfield" with thepassword "ASmi9999," and set the default database to the MyGuitarShop database.Then, grant the login ID access to the MyGuitarShop database, create a user for thelogin ID named "RBrautigan", and assign the user to the OrderEntry role you createdin 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.Save a screen print of the user list after adding this user. Name it ex17-04a.png.(Any image format will be fine)5Write a script that removes the user-defined database role named OrderEntry. (Hint:This script should begin by removing all users from this role.)6Write a script that (1) creates a schema named Admin, (2) transfers the table namedAddresses from the dbo schema to the Admin schema, (3) assigns the Admin schemaas the default schema for the user named RobertHalliday that you created in exercise2, and (4) grants all standard privileges except for REFERENCES and ALTER toRobert Halliday for the Admin schema. AdministratorsAdmin DEmailAddressPasswordFirstNameLastNameOrderitemsItem DOrder D7ProductsProductIDCategoryIDProduct CodeProductNameDescriptionListPriceProductIDItemPriceCategoriesCategoryIDDiscountAmountQuantityDiscountPercentDate AddedCategoryNameQAddressesAddressIDCustomerlDLinelLine2CityStateZipCodePhoneDisabledOrders8OrderIDCustomerlDOrderDateShipAmountTaxAmountShipDateShipAddressIDCard TypeCardNumberCardExpiresBillingAddressIDCustomersCustomer DEmailAddressPasswordFirstNameLastNameShippingAddressIDBillingAddress D

Please find the attached solution of the given problem: 1- DECLARE MyGuitarShop CURSORDYNAMICFORSELECT Orders_id, Orderitems_id FROM Orderitems;DECLARE @order varchar(50), @item varchar(50);DECLARE @initLog varchar(MAX);OPEN MyGuitarShopFETCH FIRST FROM MyGuitarShop; BEGIN WHILE @@FETCH_STATUS = 0 SET @initLog = 'CREATE LOGIN ' + @order + @item + ' WITH PASSWORD = '+'''temp'''+'MUST_CHANGE, DEFAULT_DATABASE = MyGuitarShop; CREATE USER ' + @order + @item + ' ALTER ROLE OrderEntry ADD MEMBER ' + @order + @item + ';' EXEC(@initLog) FETCH NEXT FROM MyGuitarShop; ENDCLOSE MyGuitarShop;DEALLOCATE MyGuitarShop; 2- function login (email, password, callback) { var connection = mysql({ host : 'localhost', user : 'RobertHalliday', password : 'HelloBob', database : 'MyGuitarShop' }); connection.connect(); var query = "SELECT id, nickname, email, password " + "FROM users WHERE email = ?"; connection.query(query, [email], function (err, results) { if (err) return callback(err); if (results.length === 0) return callback(); var user = results[0]; if (!bcrypt.compareSync(password, user.password)) { return callback(); } callback(null, { id: user.id.toString(), nickname: user.nickname, email: user.email }); }); } Declare @defaultDataBaseName as varchar(50);Declare @LoginCreationScript as varchar(max);Declare @UserCreationScript as varchar(max);Declare @TempUserCreationScript as varchar(max);set @defaultDataBaseName = 'MyGuitarShop';set @RobertHalliday = 'domain\RobertHalliday';set @LoginCreationScript ='CREATE LOGIN [{RobertHalliday}]FROM WINDOWSWITH DEFAULT_DATABASE ={MyGuitarShop}' set @UserCreationScript ='USE {MyGuitarShop}CREATE User [{RobertHalliday}] for LOGIN [{RobertHalliday}];EXEC sp_addrolemember ''db_datareader'', ''{RobertHalliday}'';EXEC sp_addrolemember ''db_datawriter'', ''{RobertHalliday}'';Grant Execute on Schema :: dbo TO [{RobertHalliday}];' /*User creation and role assignment*/set @TempUserCreationScript =Replace(@UserCreationScript, '{MyGuitarShop}', @defaultDataBaseName)Execute(@TempUserCreationScript)set @TempUserCreationScript =Replace(@UserCreationScript, '{MyGuitarShop}', 'db2')Execute(@TempUserCreationScript)set @TempUserCreationScript =Replace(@UserCreationScript, '{MyGuitarShop}', 'db3')Execute(@TempUserCreationScript

Engineering

Computer Science

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. Write a script that (1) creates a login ID named "BillyHalliday" with the password "HelloBilly"; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named "BHalliday" for the login; and (4) assigns the user to the OrderEntry role you created in exercise 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. Write a script that (1) creates a login ID named "BillyHalliday" with the password "HelloBilly"; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named "BHalliday" for the login; and (4) assigns the user to the OrderEntry role you created in exercise 1.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: SQL Injection is a term that refers to an attack on a SQL database

A: SUMMARY: - Hence, we discussed all the points.

Q: Python please Stream an sqlite database from a server to a client.

A: In order to stream an SQLite database from a server to a client in Python, you can use the Flask…

Q: Method: Back up MySQL Database Using phpMyAdmin

A: Backing up your MySQL database using phpMyAdmin is a fundamental aspect of database management,…

Q: As you operate on databases, you may use triggers or processes. Triggers—when and how?

A: A database trigger is a unique kind of stored procedure that is triggered to execute in response to…

Q: Explain database administrators' configuration management duties.

A: Configuration running is a vital aspect of database organization, as it involve Methodically…

Q: How do I use a SQL query at the command line to load mysql databases?

A: SQL (Structured Query Language) is a standard language for interacting with relational databases,…

Q: Database administrators are responsible for backing up the database, whereas users are responsible…

A: Introduction: As we all know, backup is a necessary procedure that must be carried out regularly. We…

Q: SQL Database Designs The database administrator is charged with managing the database. What does…

A: A database administrator (or DBA) is a role who works in the Information Technology department and…

Q: Database Administration (Explain the strategy to manage and monitor the database server) You should…

A: Physical database architecture and much of database implementation are usually handled by database…

Q: Database audit trail: what is it? In no more than 60 words, briefly describe how SQL Server makes…

A: Database audit trail refers to a record of all the activities and changes made to a database system.…

Q: How can I use a SQL query on the command prompt to import a MySql database?

A: Introduction: It is one of the most generally used relational database management systems and the…

Q: Web and database keyword searches vary greatly. What are the main differences between query…

A: Web and database keyword searches vary greatly in terms of search strategies and techniques…

Q: What are some ways that you can test your database to see if it is efficient?

A: In this answer I will be discussing about different tests performed on databases to make it…

Q: How do prepared statements help prevent SQL injection attacks?

A: SQL injection is an attack where attackers exploit application user inputs to manipulate or insert…

Q: Differentiate among the types of SQL Server statements listed below, and provide a scenario in which…

A: SQL statements are used to perform tasks such as updating records, deleting records, and retrieving…

Q: How do view tables differ from base tables in a relational database?

A: Tables in a relational database can be broadly categorized into two types: base tables and view…

Concept explainers

SQL

SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.

Queries

A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.

Question

USE MICROSOFT SQL SERVER

22
Murach's SQL Server 2016 for Developers: My Guitar Shop exercises (modified)
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 table. Give SELECT permission for all user tables.
2 Write a script that (1) creates a login ID named "BillyHalliday" with the password
"HelloBilly"; (2) sets the default database for the login to the MyGuitarShop
database; (3) creates a user named "BHalliday" 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 that consists of the
administrator's last and first name with no space between; (2) set a temporary
password of "dump" 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 "ASmithfield" with the
password "ASmi9999," 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.
Save a screen print of the user list after adding this user. Name it ex17-04a.png.
(Any image format will be fine)
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
Robert Halliday for the Admin schema.

Administrators
Admin D
EmailAddress
Password
FirstName
LastName
Orderitems
Item D
Order D
7
Products
ProductID
CategoryID
Product Code
ProductName
Description
ListPrice
ProductID
ItemPrice
Categories
CategoryID
DiscountAmount
Quantity
DiscountPercent
Date Added
CategoryName
Q
Addresses
AddressID
CustomerlD
Linel
Line2
City
State
ZipCode
Phone
Disabled
Orders
8
OrderID
CustomerlD
OrderDate
ShipAmount
TaxAmount
ShipDate
ShipAddressID
Card Type
CardNumber
CardExpires
BillingAddressID
Customers
Customer D
EmailAddress
Password
FirstName
LastName
ShippingAddressID
BillingAddress D

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

The SQL Server Management Studio is a core component of SQL Server that allows a user to create and manage their database(s). Question 19 options: True False
Write syntax for DELETE and TURNCATE in SQL databases.
Explain the role of indexing in improving database query performance.
How do you close a database record?
Do you believe that the duty to backup the database falls on the server administrator or the database administrator?
Please provide a definition for each of the following terms: database
How do you close a database record?
Explain how to use SQL queries in QTP ?
Problem 2 - K-means Use the Ecoli dataset at https://archive.ics.uci.edu/ml/datasets/Ecoli Ignore the label and create clusters using k values between 4 and 6. Pick the best k value and explain why you picked it Show any calculations you used to pick the best cluster Create two visualization One colors the nodes with the cluster membership The other colors the nodes based on the actual label Grading criteria: Adequately describe how to pick the best cluster and successful create the required visualizations ### Your code here Provide an explanation of your model: Problem 3 - Support Vector Machines Use the Iris trainging set Explore the data to find the best two features to use We are mostly doing this so we can visualize the results Split the data set into 80% training and 20% testing Create a SVM to model the data Create a visualization that shows the line and the margins Create anonther visualization that shows the decision surface Do not include the test data…
SQL Database Explain the function and importance of the SQL COMMIT command and list the pros/cons of using Autocommit mode.