数据库认证实验四答案

--1，创建登陆用户temp1,密码是1234,加入到school数据库中并赋予db_owner数据库角色. Sp_addlogin'temp1','1234','School';
---添加用户
use School
go
exec sp_grantdbaccess'temp1';
---
/*
CREATE LOGIN tmp1
WITH PASSWORD = '123',default_database=School;
-- Creates a database user for the login created above.
CREATE USER user1 FOR LOGIN tmp1;
*/
--查看登录账号的信息
Select*from sys.syslogins where name='temp1';
/*
use School
go
exec sp_addsrvrolemember 'temp1','sysadmin';--添加服务器角色
*/
--赋予db_owner数据库角色.
use School
go
exec sp_addrolemember'db_owner','temp1';
--2，创建登陆用户temp2,密码是1234,
--加入到school数据库中，并赋予该用户对student表select,insert权限
Sp_addlogin'temp2','1234','School';
---添加用户
use School
go
exec sp_grantdbaccess'temp2';
grantselect,insert
on student
to temp2;
--3，对数据库school进行完全数据库备份，写出备份语句
Use School
Go
Sp_addumpdevice'disk','schoolinfo','e:\data\schoolinfo.bak'
Go
Backupdatabase school to schoolinfo withinit;
--4，将student学生信息表中所有学生信息年龄改为20，进行差异化备份--修改表的数据
use school
go
update student
set sage=20;
---执行差异化的备份
Use School
Go
Backupdatabase School todisk='e:\data\schoolinfo.bak';
Use school
Go
Backupdatabase School to schoolinfo withdifferential,init
Use school
Go
select*from student
Backupdatabase school to schooldemo withdifferential,init
--5，对school数据库进行完整数据库备份恢复。

Restoredatabase school from schoolinfo with replace,norecovery --6，将数据库恢复到年龄为20之前，写出差异化恢复语句Restoredatabase school from schoolinfo;
--删除school数据库，
--5，对school数据库进行完整数据库备份恢复。

--6，将数据库恢复到年龄为20之前，写出差异化恢复语句。