Kobe Francisco
BT-506
05 Practice Exercise 1
October 29, 2024
ItemID ItemName Price
001 Gatorade 90.00
002
VitaMilk 35.00
GO
CREATE DATABASE[INVENTORY] GO
USE[INVENTORY] GO
CREATE TABLE [dbo].[Items] (
[ItemD] [int] NULL,
[ItemName] [nvarchar](50) NULL, [Price] [decimal](5,2) NULL,
);
INSERT [dbo].[Items] ([ItemID]), [ItemName], [Price]) VALUES (001, N ‘Gatorade’, 90.00),
(002, N ‘Vitamilk’, 35.00);
Using T-SQL commands
1. Create a login with the following credentials. username: YourName (Ex. Mark), password: pw123.
CREATE LOGIN KobeFrancisco WITH PASSWORD = ‘pw123’;
2. Create another user under the login you have created.
USE [Inventory] GO
CREATE USER user1 FOR LOGIN KobeFrancisco
3. Create a user-defined role named "udr_YourNickname" with the privilege of viewing and updating the data in the
"Items" table.
USE[Inventory] GO
CREATE ROLE [udr_KobeFrancisco] GO
GRANT SELECT, UPDATE ON [Items]
TO [udr_KobeFrancisco] GO
4. Assign the created user-defined role to the user.
USE [Inventory] GO ALTER ROLE [udr_KobeFrancisco] ADD MEMBER user1
5. Give the user access to view, update, and delete data in the "Items" table.
USE [Inventory] GO
GRANT SELECT, UPDATE, DELETE ON [Items] TO user1
6. Remove the role you have assigned to that user.
USE [Inventory] GO
ALTER ROLE [udr_KobeFrancisco] DROP MEMBER user1 GO
DROP ROLE [udr_KobeFrancisco]
7. Change the access of that user by removing his privilege to delete data in the "Items" table.
REVOKE DELETE ON [Items] TO user1
