/* Q1. As an administrator of your SQL Server, write a DDL statement to create a

Question

/* Q1.
As an administrator of your SQL Server, write a DDL statement to create a new SQL Server login
called 'shark' with a password 'Mid-Atlantic'. Do not enforce the Windows password policies on
this login.
*/

/* Q2.
Do a little research to find what GRANT statement needs to execute if we allow the above 'shark'
login to create another login called 'bluewhale' with a password 'All-oceans'.
*/

/* Q3.
Assuming a user is already connected to a SQL Server using a login named 'Anna', what
statement does this user need to execute in order to switch the execution context from
'user1' to another, say, 'Jack', in order to run scripts or statements based on 'Jack'?
What statement is needed to switch the execution context back to 'Anna'?
*/

--To switch the execution context from login 'Anna' to another login 'Jack':

--To switch the execution context from 'Jack' back to 'Anna':

/* Q4.
Assuming the login 'Anna' was already created in your server but it has no permissions
yet to use the Clearwater database. What statement will you execute to make 'Anna' be
a user named 'assistant' of Clearwater in your server?

*Do not forget to set first the database context to Clearwater by a USE statement. If
you forget, this new user 'assistant' will be added to the current database you are in.
*/

/* Q5.
Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server,
write one GRANT statement to perform two tasks: (1) allow this user to create new tables in
Clearwater, and (2) allow this user to grant the same permission (i.e., creating new tables
in Clearwater) to other users.
*/

/* Q6.
Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server,
write a statement to make this user a member of 'db_datareader' database role of ClearWater.
*/

/* Q7.
Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server,
write a statement to allow this user to update UnitPrice and UnitsInStock values of Products
table.
*/

/* Q8. (8 points)
Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server,
write a REVOKE statement to disallow this user to read the Address and Phone values of Suppliers.

After the above REVOKE statement is executed, is user 'assistant' still able to read the Address
and Phone values of Suppliers? Why?

If REVOKE statement does not work for this purpose, what statement should be used? Hint: p.562-3
*/

--(2 points) the REVOKE statement:

--(4 points) Can 'assistant' still read Address and Phone of Suppliers after the REVOKE is done? Why?

--(2 points) What statement other than REVOKE is needed?

/* Q9.
Assuming you are currently working on a query editing page or session of Clearwater database
in your SQL Server using your own login, what statement do you need to execute in order to
switch the execution context from yours to the 'assistant' user, that is, pretending you are
the 'assistant' user of Clearwater? What statement is needed to switch the execution context
back to your own?
*/

--To switch the execution context from yours to the 'assistant' user:

--To switch the execution context from the 'assistant' user back to your own:

/* Q10.
In Q5, you have granted the 'assistant' user a permission of CREATE TABLE in the Clearwater
database and also allowed this user to grant the same permission to other users of this
database. A big concern and risk of doing so is the 'assistant' user and everyone who was
granted this permission can keep granting to more and more other users, which may become out
of your expectation eventually.

To stop and prevent the 'assistant' user and all who were granted such permission from granting
to even more other users, what statement will you execute?
*/

Explanation / Answer

Solution:

Note: The first three subparts have been answered as per Chegg guidelines, please repost others.

1)

3)

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.