用户与权限管理

\一、实验目的

1、了解SQL Server 2000中的两种用户登录模式；登录帐户、用户、角色、权限的

概念；

2、熟练掌握企业管理控制台方式管理用户、角色、权限的方法。熟练掌握命令行方

式管理用户、角色、权限的命令。

3、熟悉数据库备份及恢复机制、了解SQL Server的数据备份和恢复机制、掌握

SQL-Server中数据库备份和恢复的方法。

4、了解游标的作用

5、理解存储过程的概念，掌握创建与运行存储过程的一般方法，加深对客户/服务器机制的理解。掌握触发器的创建方法，学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解。比较存储过程和触发器的异同。

二、实验内容

恢复上次课的数据库表结构和数据。

（一）在图形化界面中完成以下操作

（1）创建一个登录用户

（2）将所创建的登录用户加为某数据的用户

（3）自定义一个角色

（4）对所建登录用户、数据库用户和角色授权

A、给用户授予固定服务器角色和给用户授予固定数据库角色

B、给用户和角色授予一些对象权限。

C、将用户自定义角色授予给用户。

（5）删除数据库用户和登录用户

（6）数据库的备份与恢复

A、为test数据库创建完全数据库备份test.bak，目标磁盘为D\ test.bak。

B、在test数据库中新建数据表stu，然后对test数据库创建差异备份。

C、向stu表插入部分记录后对test数据库创建事务日志备份。

D、恢复test数据库到最初状态。

E、将test数据库恢复到创建数据表stu后的状态。

F、将test数据库恢复到在stu表插入记录后的状态。

（二）用命令完成以下操作

1、用户管理

（1）、用系统存储过程sp_addlogin（或CREATE LOGIN），以自己的姓名加上学号为用户名创建一个登录帐户。

（2）、用系统存储过程sp_defaultdb、sp_defaultlanguage（或ALTER LOGIN）修改帐户修改默认数据库和默认语言，sp_password修改帐户密码。

（3）用sp_droplogin（或DROP LOGIN）删除 SQL Server标准登录帐户。

（4）用sp_grantdbaccess（或用CREATE USER）添加数据库用户

（5）用sp_revokedbaccess （或用DROP USER ）删除数据库用户

2、角色管理

（1）用命令sp_addrole或CREATE ROLE，以姓名加上学号为角色名创建一个角色，并使用名字的汉语拼音作为口令；

（2）用ALTER ROLE修改创建的角色，去掉口令。

（3）用sp_drorole（或 drop role ）删除角色

3、权限管理

(1)对象权限的管理

A、分别用SA用户和新建用户打开两个SSMS

B、在新建用户窗口查询student表中的数据，观察查询结果

C、在SA窗口，用grant命令把student表的查询和修改权限授给新建用户

D、在新建用户窗口查询student表中的数据，观察查询结果

E、在SA窗口，用revoke命令收回新建用户对student表的查询权限

F、在新建用户窗口查询student表中的数据，观察查询结果

（2）语句权限的管理

A、分别用SA用户和新建用户打开两个SSMS

B、在新建用户窗口创建一个table，观察查询结果

C、在SA窗口，用grant命令把create table权限授给新建用户

D、在新建用户窗口创建一个table，观察查询结果

E、在SA窗口，用revoke命令收回新建用户的create table权限

F、在新建用户窗口创建一个table，观察查询结果

4、数据库的备份与恢复（选作）

(1)为test数据库创建完全数据库备份test.bak，目标磁盘为D\ test.bak。

(2)在test数据库中新建数据表stu，然后对test数据库创建差异备份。

(3)向stu表插入部分记录后对test数据库创建事务日志备份。

(4)恢复test数据库到最初状态。

(5)将test数据库恢复到创建数据表stu后的状态。

(6)将test数据库恢复到在stu表插入记录后的状态。

5、游标的操作

用T-SQL语句声明一个游标S_Cursor，查询Student表中所有男生的信息，并读取数据。

(1) 读取最后一条记录。

(2) 读取第一条记录。

(3) 读取第5条记录。

(4) 读取当前记录指针位置后第3条记录。

DECLARE S_Cursor SCROLL CURSOR

FOR SELECT* FROM Student W HERE S SEX='男'

OPEN S_Cursor

FETCH LAST FROM S_Cursor

FETCH PRIOR FROM S_Cursor

FETCH ABSOLUTE 5 FROM S_Cursor

FETCH relative 3 FROM S_Cursor

6、存储过程与触器

（1）创建一个名为GRADE_INFO的存储过程，其功能是查询某门课程的所有学生成绩。显示字段为：CNAME，SNO，SNAME，GRADE。

（2）创建一个INSERT类触发器TR_C_INSERT，当在SC表中插入一条新记录时，触发该触发器，检查该记录的学号在student表中是否存在，如果不存在，则操作失败并给出“该学生不存在”的提示信息.

四.实验习题源程序（SQL命令）清单与运行结果

use ＨＨ

1、用户管理

（1）、用系统存储过程sp_addlogin（或CREATE LOGIN），以自己的姓名加上学号为用户名创建一个登录帐户。

CREATE LOGIN HJ WITH password='201310801009'

（2）、用系统存储过程sp_defaultdb、sp_defaultlanguage（或ALTER LOGIN）修改帐户修改默认数据库和默认语言，sp_password修改帐户密码。

sp_defaultdb 'HJ','hh'

sp_defaultlanguage 'HJ','english'

sp_password '201310801009','123456','HJ'

（3）用sp_droplogin（或DROP LOGIN）删除 SQL Server标准登录帐户。

EXEC sp_droplogin 'HJ'

（4）用sp_grantdbaccess（或用CREATE USER）添加数据库用户

sp_grantdbaccess 'HJ','TEST'

（5）用sp_revokedbaccess （或用DROP USER ）删除数据库用户

sp_revokedbaccess 'HJ'

2、角色管理