SQL 2005 database security and referential integrity

admin · Posted on 11/26/2014 3:47:16 PM

--Create a login account
create login username with password='password'
--Create database user
create user username for login username
--- grant the user query permission to the Student table and grant it further authorizationotherRights of users.
grant select
on student
to user_tom
with grant option
-----
create login marry with password='marry001'
create user user_marry for login marry
--Create a character
create role dbnormuser
--- authorize roles
grant select
on student
to dbnormuser
--Specify a fixed server role (login)
sp_addsrvrolemember marry,sysadmin
Delete
sp_dropsrvrolemember marry,sysadmin
--View users
sp_helpuser
--- create a new foreign key constraint
ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;
--- remove foreign keys
ALTER TABLE test_sub drop CONSTRAINT main_id_cons
--Create cascading removal foreign keys
ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main on delete
cascade
--Create cascade update foreign keys
ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main on update
cascade
--Create and delete the referenced primary key is the foreign key of the key reference relationship is set to null
ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main on update
set null

---- Reference completeness
You can't delete a record with a foreign key
|

[Source] SQL 2005 database security and referential integrity

Related Posts