log-in (admin&user) that admin and user can use my sql database

Question

so i have a sql database and my only problem is i dont know how to put log-in security where admin can do CRUD and user can do CRU.Please help me im stuck....

Answer 1

`
GRANT INSERT, SELECT, UPDATE, DELETE TO admin
GRANT INSERT, SELECT, UPDATE TO plainuser

Answer 2

Hi @samidi ,

Could you please describe your issue in more detail?

you can manage your login accout from SSMS (SQL Server Management Studio)

Usually, when you use SSMS, there will be default SA account that can manage all your database

You can also set up User Mappings in the Login Properties to allow your users to manage specific databases

https://www.red-gate.com/simple-talk/databases/sql-server/learn/sql-server-authentication-methods/

Answer 3

Thus what my teachent wants me to do :

The time frame or schedule for each section of the project to complete(tasks)
The tables with the elements and their description and data type
The menu options
The screen for operation or for interaction
The security
What belongs in the each of the crude:
Such as operation, table, element and sample output expected
The user of the system and the area in the operation they are able to have access on
The security

and This is my sql database Code:

create database crmss12

                        /*CLINIC RECORD MANAGEMENT SYSTEM-*/

/MENU/
SELECT * FROM Elems_Dept;
SELECT * FROM Juniors_Dept;
SELECT * FROM Seniors_Dept;
SELECT * FROM Colleges_Dept;
SELECT *FROM Elems_level;
SELECT *FROM Juniors_level;
SELECT *FROM Seniors_level;
SELECT *FROM College_courses;
SELECT *FROM vitalSigns;
SELECT *FROM medicines;
SELECT *FROM complaint;
SELECT *FROM clinic;
/*UPDATE */
UPDATE Elem_Dept
SET FIRST_NAME = 'Mary'
WHERE E_ID = '2';

/Dlete/
DELETE FROM Elem_Dept WHERE E_ID = '2';
DELETE FROM complaints WHERE comp_ID = '6';

/SEARCH Elementary Department/
SELECT Last_name,Grade_level,sec
FROM Elem_Dept, Elem_level
WHERE Elem_Dept.E_ID = Elem_level.El_ID

/SEARCH Junior Department/
SELECT Last_name,Grade_level,sec
FROM Junior_Dept, Junior_level
WHERE Junior_Dept.J_ID = Junior_level.Jl_ID

/SEARCH Senior Department/
SELECT Last_name,Grade_level,sec,strand
FROM Senior_Dept, Senior_level
WHERE Senior_Dept.S_ID = Senior_level.Sl_ID

/SEARCH College Department/
SELECT Last_name,Course,Year_level
FROM College_Dept, College_course
WHERE College_Dept.C_ID = College_course.Cc_ID

/* Select the first name and last name of the elementary department ,thier vital signs,medicine,complaints,nurse name*/
SELECT first_name,Last_name,Grade_level,sec,temps,medicine_name,Quantity,Illness,Remarks,Nurse_name
FROM Elem_Dept
INNER JOIN Elem_level
ON Elem_Dept.E_ID=Elem_level.El_ID
INNER JOIN vitals
ON vitals.V_ID=Elem_level.El_ID
INNER JOIN medicine
ON medicine.med_ID=vitals.V_ID
INNER JOIN complaints
ON complaints.comp_ID=medicine.med_ID
INNER JOIN clinic_dept
ON complaints.comp_ID=clinic_dept.clinic_ID

/* Select the first name and last name of the elementary department ,thier vital signs,medicine,complaints,nurse name*/

SELECT First_name,Last_name,Age,Address,Grade_level,sec,temps,rr,pr,Ht,Wt,medicine_name,Quantity,Illness,Remarks,Nurse_name
FROM Elems_Dept
INNER JOIN Elems_level
ON Elems_Dept.E_ID=Elems_level.El_ID
INNER JOIN vitalSigns
ON vitalSigns.V_ID=Elems_level.El_ID
INNER JOIN medicines
ON medicines.med_ID=vitalSigns.V_ID
INNER JOIN complaint
ON complaint.comp_ID=medicines.med_ID
INNER JOIN clinic c
ON complaint.comp_ID=c.clinic_ID

BELOW ARE THE CODES
																																																																																																																																																*/

/TABLES/

CREATE TABLE Elems_Dept
(
E_ID INT NOT NULL,
First_name VARCHAR(50),
Middle_name VARCHAR(50),
Last_name VARCHAR(50),
Age INT,
DOB DATE,
Address VARCHAR(50),
CONSTRAINT PK_Elem_Dept PRIMARY KEY(E_ID)

);
CREATE TABLE Juniors_Dept
(
J_ID INT NOT NULL,
First_name VARCHAR(50),
Middle_name VARCHAR(50),
Last_name VARCHAR(50),
Age INT,
DOB DATE,
Address VARCHAR(50),
CONSTRAINT PK_Junior_Dept PRIMARY KEY(J_ID)
);
select * from Juniors_Dept;
CREATE TABLE Seniors_Dept
(
S_ID INT NOT NULL,
First_name VARCHAR(50),
Middle_name VARCHAR(50),
Last_name VARCHAR(50),
Age INT,
DOB DATE,
Address VARCHAR(50),
CONSTRAINT PK_Senior_Dept PRIMARY KEY(S_ID)
);
select *from Seniors_Dept;
CREATE TABLE Colleges_Dept
(
C_ID INT NOT NULL,
First_name VARCHAR(50) NOT NULL,
Middle_name VARCHAR(50),
Last_name VARCHAR(50)NOT NULL,
Age INT,
DOB DATE,
Address VARCHAR(50),
CONSTRAINT PK_Colleges_Dept PRIMARY KEY(C_ID)
);
select *from Colleges_Dept;
CREATE TABLE Elems_level
(
El_ID INT NOT NULL,
Grade_level VARCHAR(50),
Sec VARCHAR(50),
CONSTRAINT PK_Elems_level PRIMARY KEY(El_ID)
);
select from Elems_level;
CREATE TABLE Juniors_level
(
Jl_ID INT NOT NULL,
Grade_level VARCHAR(50),
Sec VARCHAR(50),
CONSTRAINT PK_Juniors_level PRIMARY KEY(Jl_ID)
);
selectfrom Juniors_level;
CREATE TABLE Seniors_level
(
Sl_ID INT NOT NULL,
Grade_level VARCHAR(50),
Sec VARCHAR(50),
strand VARCHAR(50),
CONSTRAINT PK_Seniors_level PRIMARY KEY(Sl_ID)
);
select from Seniors_level;
CREATE TABLE College_courses
(
Cc_ID INT NOT NULL,
Course VARCHAR(50),
Year_Level VARCHAR(50),
CONSTRAINT PK_College_courses PRIMARY KEY(Cc_ID)
);
select from College_courses;
CREATE TABLE vitalSigns
(
V_ID INT NOT NULL,
temps float,
rr INT,
pr INT,
Ht INT,
Wt INT,
CONSTRAINT PK_vitalSigns PRIMARY KEY(V_ID)
);
select*from vitalSigns;
CREATE TABLE medicines
(
med_ID INT NOT NULL,
medicine_name VARCHAR(50),
Quantity INT,
CONSTRAINT PK_medicines PRIMARY KEY(med_ID)
);
select *from medicines;
CREATE TABLE complaint
(
comp_ID INT NOT NULL,
Illness VARCHAR(100),
Remarks VARCHAR(250),
CONSTRAINT PK_complaint PRIMARY KEY(comp_ID)
);
select *from complaint;
CREATE TABLE clinic
(
clinic_ID INT NOT NULL,
Nurse_name VARCHAR(50),
Student_assistant VARCHAR(50),
CONSTRAINT PK_clinic PRIMARY KEY(clinic_ID)
);
SELECT *FROM clinic;

/*CRUD OPERATION-INSERT/CREATE*/

INSERT INTO Elems_Dept (E_ID,First_name,Middle_name,Last_name,Age,DOB,Address)
VALUES
(1,'Juvy','E','Mahinay','7','07/12/2014','Calangag Bacong'),
(2,'Sammy','T','Ramirez','8','06/10/2013','colon Streeet'),
(3,'Jose','A','Martinez','9','01/08/2012','Talay Dumaguete'),
(4,'Chris','L','Lorito','9','07/10/2012','Mangnao Dumaguete City'),
(5,'Manual','C','Sarita','6','07/12/2015','Cervantes Street');
SELECT * FROM Elems_Dept;

INSERT INTO Juniors_Dept (J_ID,First_name,Middle_name,Last_name,Age,DOB,Address)
VALUES
(1,'Mark','U','Discaya','14','01/01/2006','Sibulan'),
(2,'Dale','A','Gador','17','07/11/2004','Bagacay'),
(3,'Sofia','M','Bulagao','18','01/09/2003','Tinago Dumaguete'),
(4,'Cathy','N','Austero','15','07/07/2006','Poblacion 8'),
(5,'Chris','T','Emperado','6','07/05/2010','Boloc-boloc Sibulan');
SELECT * FROM Juniors_Dept;

INSERT INTO Seniors_Dept (S_ID,First_name,Middle_name,Last_name,Age,DOB,Address)
VALUES
(1,'Pamela','A','Amorado','16','2005-08-03 11:55:00','Dumaguete City'),
(2,'Maya','V','Soberano','17','2004-09-05 10:45:40','Upper LUke wright'),
(3,'Marie','O','Test','18','2003-05-12 09:45:00','Purok mangga'),
(4,'Jose','L','Sargento','17','2004-09-10 08:35:00','Poblacion 1'),
(5,'Mike','A','Enoferio','16','2005-03-01 01:23:00','Cambagroy');

SELECT *FROM Seniors_Dept

INSERT INTO Colleges_Dept (C_ID,First_name,Middle_name,Last_name,Age,DOB,Address)
VALUES
(1,'Sheilla','P','Agustines','20','2001-11-25 03:43:00','Colon Street'),
(2,'Jenny','H','Solamillo','22','1999-01-02 01:23:40','Banilad Dumaguete City'),
(3,'Gail','Y','Santos','21','2000-12-09 11:43:30','Bais City'),
(4,'Elma','M','Mayono','25','1996-01-25 12:23:00','San jose extension'),
(5,'Rosalina','A','Sabaricos','27','1994-12-12 07:43:50','Amlan');
SELECT * FROM Colleges_Dept;

INSERT INTO Elems_level (El_ID,Grade_Level,Sec)
VALUES
(1,'Grade 1', 'Sampaguita'),
(2,'Grade 2', 'Lilly'),
(3,'Grade 3', 'Rose'),
(4,'Grade 4', 'Orchid'),
(5,'Grade 5', 'Tulip'),
(6,'Grade 6', 'Daisy');
SELECT * FROM Elems_level;

INSERT INTO Juniors_level (Jl_ID,Grade_Level,Sec)
VALUES
(1,'Grade 7', 'Melon'),
(2,'Grade 8', 'Lemon'),
(3,'Grade 9', 'Strawberry'),
(4,'Grade 10', 'cherry');
SELECT * FROM Juniors_level;

INSERT INTO Seniors_level (Sl_ID,Grade_Level,Sec,strand)
VALUES
(1,'Grade 11', 'Mars','GAS'),
(2,'Grade 11', 'Jupiter','HUMSS'),
(3,'Grade 11', 'Saturn','STEM'),
(4,'Grade 11', 'Mercury','ABM'),
(5,'Grade 12', 'Venus','GAS'),
(6,'Grade 12', 'Neptune','HUMSS'),
(7,'Grade 12', 'Uranus','STEM'),
(8,'Grade 12', 'Earth','ABM');
SELECT * FROM Seniors_level;

INSERT INTO College_courses(Cc_ID,Course,Year_level)
VALUES
(1,'College of Liberal Arts-Education', 'First Year'),
(2,'College of Liberal Arts-Education', 'Second Year'),
(3,'College of Liberal Arts-Education', 'Third Year'),
(4,'College of Liberal Arts-Education', 'Fourth Year'),
(5,'College of Business Education', 'First Year'),
(6,'College of Business Education', 'Second Year'),
(7,'College of Business Education', 'Third Year'),
(8,'College of Business Education', 'Fourth Year'),
( 9,'College of Criminal Justice Education', 'First Year'),
(10,'College of Criminal Justice Education', 'Second Year'),
(11,'College of Criminal Justice Education', 'Third Year'),
(12,'College of Criminal Justice Education', 'Fourth Year'),
(13,'College of Allied Health Sciences', 'First Year'),
(14,'College of Allied Health Sciences', 'Second Year'),
(15,'College of Allied Health Sciences', 'Third Year'),
(16,'College of Allied Health Sciences', 'Fourth Year');

SELECT * FROM College_courses;

INSERT INTO vitalSigns(V_ID,temps,rr,pr,Ht,Wt)
VALUES
(1,'36.5','60','16','155','45'),
(2,'35','50','17','186','54'),
(3,'36.9','50','17','176','44'),
(4,'36.8','70','17','159','40'),
(5,'37.5','40','17','160','49');
SELECT * FROM vitalSigns;

INSERT INTO medicines(med_ID,medicine_name,Quantity)
VALUES
(2,'Tempra','2'),
(3,'Biogesic','7'),
(4,'Medicol','5'),
(5,'Diatabs','4'),
(6,'Stresstab','3');
SELECT * FROM medicines;

INSERT INTO complaint (comp_ID,Illness,Remarks)
VALUES
(2,'Fever','Need to rest and take Tempra 3 times a day to reduce fever'),
(3,'Fever','Need to rest and take Tempra 3 times a day to reduce fever'),
(4,'headace','Need to rest and take Tempra 3 times') ,
(5,'diarrhea','Need to rest and take diatabs') ,
(6,'stresstab','Need to rest and take stresstab') ;

SELECT * FROM complaint;

INSERT INTO clinic (clinic_ID,Nurse_name,Student_assistant)
VALUES
(1,'Jade Caturay','Marienol E. Esong');
SELECT * FROM clinic;

INSERT INTO Statuses(stat_ID,active,not_active)
VALUES
(1,'Yes','No');
SELECT * FROM Statuses;

/UPDATE EXAMPLE/
UPDATE Elem_Dept
SET FIRST_NAME = 'Mary'
WHERE E_ID = '1';

/Dlete EXAMPLE/
DELETE FROM Elems_Dept WHERE E_ID = '5';

/SEARCH Elementary Department/
SELECT Last_name,Grade_level,sec
FROM Elems_Dept, Elems_level
WHERE Elems_Dept.E_ID = Elems_level.El_ID

/SEARCH Junior Department/
SELECT Last_name,Grade_level,sec
FROM Juniors_Dept, Juniors_level
WHERE Juniors_Dept.J_ID = Juniors_level.Jl_ID

/SEARCH Senior Department/
SELECT Last_name,Grade_level,sec,strand
FROM Seniors_Dept, Seniors_level
WHERE Seniors_Dept.S_ID = Seniors_level.Sl_ID

/SEARCH College Department/
SELECT Last_name,Course,Year_level
FROM Colleges_Dept, College_courses
WHERE Colleges_Dept.C_ID = College_courses.Cc_ID

P.S THANK YOU EVERYONE FOR GIVING TIME ...