SQL server2005 (database security and referential integrity)

--Create Login account

Create login username with password= ' password '

--Create a database user

Create user username for login username

---Grant the user the right to query the student table, as well as to grant it further authorization to other users.

Grant Select

On student

To User_tom

With GRANT option

-----

Create login marry with password= ' marry001 '

Create user User_marry for login marry

--Creating roles

Create role Dbnormuser

---The role authorization

Grant Select

On student

To Dbnormuser

--Specify a fixed server role (login name)

Sp_addsrvrolemember Marry,sysadmin

Delete

Sp_dropsrvrolemember Marry,sysadmin

--View Users

Sp_helpuser

---new FOREIGN KEY constraint

ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES Test_main;

---delete a foreign key

ALTER TABLE test_sub Drop CONSTRAINT main_id_cons

--Create a cascading delete foreign key

ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES Test_main on Delete

Cascade

--Create a cascading update foreign key

ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main on update

Cascade

--Create a delete referenced primary key is the foreign key of the key reference relationship set to NULL

ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main on update

Set NULL

----Referential integrity

You cannot delete a record with a foreign key