SQL SERVER QUESTION Use the following database: https://codeshare.io/aJQQZ6 Use

Question

SQL SERVER QUESTION

Use the following database: https://codeshare.io/aJQQZ6

Use   USER_TAB_PRIVS and SYSTEM_PRIVILEGE_MAP to display every step you created;

Create user name developer with password developer

Change the password for the developer user to developer1

Create Role Name DeveloperRole

Grant SELECT, INSERT,UPDATE, DELETE privilege to DeveloperRole

Create user name webdev with the password webdev

Create user name appdev with the password appdev

Grant permission webdev, appdev to DeveloperRole

Remove webdev from DeveloperRole permission

Remove webdev2 from DeveloperRole permission

Drop DeveloperRole role

Create Role Name SPOwner

Grant SPOwner full permission of SP database

Drop SPOwner role

Create Role Name Admin

Grant Admin Role full permission of the system wide

Drop Admin Role.

Create user name tempuser with password tempuser

Lock tempuser’s user account

Unlock tempuser’s user account

Force tempuser user to change his or her password.

Drop termpuser.

Explanation / Answer

Hi,
1. to create a user we need to use the following command on sql server
CREATE USER developer WITH PASSWORD = 'developer';
2.to change the password we need to use the alter command
ALTER LOGIN developer WITH PASSWORD = 'developer1';
3.to create role we need to use
CREATE ROLE DeveloperRole
4. to grant privileges we need to use
GRANT SELECT,INSERT,UPDATE, DELETE to DeveloperRole
5.same as 1st part
CREATE USER webdev WITH PASSWORD = 'webdev';
6. CREATE USER appdev WITH PASSWORD = 'appdev';
7. to grant we need to use
GRANT webdev,appdev to DeveloperRole
8. to remove permissions we need to use REVOKE command
REVOKE webdev FROM DeveloperRole
9.REVOKE appdev FROM DeveloperRole
11.DROP ROLE DeveloperRole
12. same as the above creation of role
CREATE ROLE SPOwner;
13.for granting full permissions we need to use all arguement like
GRANT ALL on SCHEMA::SP to SPOwner
14.to drop role we need to use drop role as
DROP ROLE SPOwner;
15.CREATE ROLE Admin;
16. agin for dropping,
DROP ROLE Admin;
17. same as 1st part
CREATE USER tempuser WITH PASSWORD = 'tempuser';
18. to lock the user we can use the disable command
ALTER LOGIN tempuser DISABLE;

19. to unlock we need to use enable ALTER LOGIN tempuser ENABLE;
20. to force the user to change password we need to use
ALTER LOGIN tempuser with password='tempuser' MUST_CHANGE, CHECK_EXPIRATION = ON;

Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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