《数据库系统原理》实验指导书参考答案(计本10、电商10)

浙江树人大学信息科技学院

《数据库系统原理》

实 验 指 导 书

适合专业：计算机科学与技术本科专业

编写部门：电子商务教研室

编写日期：2012.02

实验一：SQL Server 2005服务器管理 ............................. 1 实验二：创建和管理数据库 ................................................ 4 实验三：数据定义 ................................................................ 4 实验四：数据更新 .............................................................. 11 实验五：数据查询1——简单查询 ................................... 15 实验六：数据查询（2）—连接查询 ................................ 17 实验七：数据查询（3）—综合 ........................................ 20 实验八：视图 ...................................................................... 21 实验九：约束、默认、规则 .............................................. 25 实验十：存储过程 .............................................................. 29 实验十一：触发器（1） .................................................... 34 实验十二：触发器（2） .................................................... 39 实验十三：安全性管理 ...................................................... 46 实验十四：数据库的备份与还原 ...................................... 47

实验一：SQL Server 2005服务器管理

一、 实验目的

通过实验使学生掌握SQL Server 2005数据库服务器启动、暂停、停止的方法；掌握SQL Server 2005数据库服务器的注册方法。

二、 原理解析

1、 SQL Server 2005服务器注册

注册服务器就是在SQL Server Management Studio中登记服务器，然后把它加入到一个指定的服务器组中，并在SQL Server Management Studio中显示SQL Server服务器的运行状态和在SQL Server Management Studio连接时自动启动SQL Server服务器。

2、 SQL Server 2005的各种实用工具

? SQL Server Management Studio

SQL Server提供了多种实用工具来帮助用户管理和使用数据库，这些工具大大方便了用户的工作。

SQL Server Management Studio是微软管理控制台中的一个内建控制台，用来管理所有的SQL Server数据库。

? SQL Server Management Studio查询窗口

SQL Server Management Studio查询窗口是一个图形界面的查询工具，用它可以提交Transact-SQL语言，然后发送到服务器，并返回执行结果，该工具支持基于任何服务器的任何数据库连接。

? 性能监视器

SQL Server提供了监视服务器性能和活动的功能，其功能是在选择的计数器上设置阀值，当达到阀值时，产生警告。利用性能监视器可以创建、观察和保存功能图表，用户可以选择对象和计数器并把它们添加到图表中，不同颜色的图表代表不同的计数器，如果要使图表较小并且可读，可以创建不同的图表来监视不同的统计类型。

? 活动监视器

SQL 活动监视器是图形工具，使系统管理员得以监视SQL Server 2005实例中的事件，可以捕获有关每个事件的数据并将其保存到文件或SQL Server表中供以后分析。

? SQL Server 2005联机丛书

提供如何使用SQL Server 2005的各种帮助。

1

3、 SQL Server 2005的常用属性配置

? ?

SQL Server 2005的登录方式设置 SQL Server 2005的连接设置

4、 SQL Server 2005服务器的运行管理 SQL Server 2005服务器的启动、暂停和停止。 三、 实验内容

1、 启动、暂停和停止SQL Server实例 2、 熟悉SQL Server 2005的各种实用工具 3、 SQL Server 2005服务器注册

四、 实验步骤

1、 启动、暂停和停止SQL Server实例 ? 利用SQL Server Management Studio

在已注册的服务器或对象资源管理器中，右键单击要启动的服务器实例，然后单击“启动”。 如果服务器名称旁边的图标上出现一个绿色箭头，则说明服务器已成功启动。

用同样的方法可以暂停和停止SQL Server实例。 ? 利用SQL Server配置管理器

（1）在“开始”菜单中，依次指向“所有程序”、“Microsoft SQL Server 2005”和“配置工具”，然后单击“SQL Server 配置管理器”。

（2）在 SQL Server 配置管理器中，展开“服务”，再单击 SQL Server。

（3）在详细信息窗格中，右键单击 SQL Server (MSSQLServer)，再单击“启动”。

如果工具栏上和服务器名称旁的图标上出现绿色箭头，则指示服务器已成功启动。 用同样的方法可以暂停和停止SQL Server实例。 2、 熟悉SQL Server 2005的各种实用工具 ? SQL Server Management Studio

? SQL Server Management Studio查询窗口 ? 练习使用SQL Server 2005联机丛书 3、 SQL Server 2005服务器注册

? 在对象资源管理器中，右键单击已经连接的服务器，然后单击“注册”。

? 在“注册服务器”对话框的“服务器名称”文本框中，键入希望显示在“已注册的服务

器”中的该服务器的名称。此项并不要求一定是服务器名称。

2

? 在“服务器说明”文本框中，可以根据需要键入一些其他信息，以帮助标识服务器。 ? 在“选择服务器组”框中，单击服务器组，再单击“保存”。

3

实验二：创建和管理数据库

一、实验目的

通过实验使学生掌握SQL Server 2005数据库的文件结构，数据库的创建和修改方法。

二、原理解析

5、 SQL Server 2005数据库文件和文件组

SQL Server 2005用文件来存放数据库，即将数据库映射到操作系统文件上。数据库文件有主数据文件、次数据文件、事务日志文件。主数据文件的后缀.mdf，次数据文件的后缀.nfd，事务日志文件的后缀.ldf。一个数据库有且仅有一个主文件，事务日志文件至少一个，次数据文件可有可无。

SQL Server 2005中提供了两种类型的文件组：主文件组和用户自定义文件组。

6、 SQL Server 2005数据库类型

SQL Server 2005数据库分为系统数据库和用户数据库。系统数据库包括master数据库、tempdb数据库、model数据库和msdb数据库。

7、 SQL Server 2005数据库的创建方法

SQL Server 2005数据库可以使用SQL Server Management Studio创建，也可以使用T-SQL语句来创建。用SQL Server Management Studio创建数据库比较简单，只要设置好数据库名和主文件、次文件以及日志文件的位置和逻辑名、物理名就可以了。下面介绍用T-SQL的CREATE DATABASE语句创建数据库。

CREATE DATABASE database_name [ON

[[,?n]] [,[,?n]]] [LOG ON {[,?n]}] [COLLATE ] [FOR LOAD|FOR ATTACH] ::= [PRIMARY] (NAME='逻辑文件名',

FILENAME='存放数据库的物理路径和文件名' [, SIZE=数据文件的初始大小]

4

[, MAXSIZE=指定文件的最大大小] [, FILEGROWTH=指出文件每次的增量]）

8、 SQL Server 2005数据库的修改

SQL Server 2005数据库的修改主要包括括修改数据库选项、扩充分配给数据库的数据或事务日志空间、压缩分配给数据库的数据或事务日志空间、添加数据和事务日志文件、创建文件组等。本次实验要求掌握使用企业管理器工具和T-SQL语句及系统存储过程对数据库进行管理。

SQL Server 2005数据库的修改可以在SQL Server Management Studio中完成，也可以使用T-SQL的ALTER DATABASE语句进行。

ALTER DATABASE语句的语法结构如下： ALTER DATABASE database_name

{ADD FILE [,?n][TO FILEGROUP filegroup_name]

/*在文件组中增加数据文件*/

|ADD LOG FILE [,…n] /*增加事务日志文件*/ |REMOVE FILE logical_file_name /*删除数据文件*/ |ADD FILEGROUP filegroup_name /*增加文件组*/ |REMOVE FILEGROUP filegroup_name /*删除文件组*/

|MODIFY FILE /*修改文件属性*/ |MODIFY NAME=new_dbname /*更新数据库名称*/ }

三、实验内容

4、 用SQL Server Management Studio创建数据库 5、 用CREATE DATABASE语句创建数据库 3、 用SQL Server Management Studio修改数据库 4、 用ALTER DATABASE语句修改数据库

四、实验步骤

1、利用SQL Server Studio创建学生数据库，数据文件的存放路径和个数可以自己设定。 2、用CREATE DATABASE语句来创建一个名为BOOK的数据库，它由2MB的主数据文

5

件、5MB的次数据文件和5MB的日志文件组成。并且主数据文件以1MB的增长速度其最大数据文件的大小为10MB，次数据文件以2MB的增长速度其最大次数据文件的大小为15MB，事务日志文件以1MB的增长速度其最大日志文件的大小为10MB。

create database book on

(name=book1,

filename='C:\\SQLServerBook2\\book1.mdf', size=2,

maxsize=10, filegrowth=1), (name=book2,

filename='C:\\SQLServerBook2\\book2.ndf', size=5,

maxsize=15, filegrowth=2) LOG ON

(name=book_log,

filename='C:\\SQLServerBook2\\book_log.ld', size=5,

maxsize=10， filegrowth=1)

3、在SQL Server Studio中将学生数据库的主文件大小改为4MB。

4、用ALTER DATABASE语句修改BOOK数据库，为其增加一个次要数据文件（Book_data2.ndf），该文件的大小是5MB，最大可增长到20MB，以20%的速度增长。

alter database book add file

(name=book_data2,

filename='c:\\sqlServerbook\\book_data2.ndf', size=5MB, maxsize=20MB, filegrowth=20%)

9、 利用SQL Server Studio为学生数据库增加一个文件组MyFilegroup。 提示：通过数据库属性→文件组进行修改。 10、

用ALTER DATABASE语句为BOOK数据库增加一个文件组MyFilegroup。

alter database Student add filegroup MyFilegroup

6

7、用ALTER DATABASE语句为BOOK数据库增加两个次数据文件，并将他们放到MyFilegroup文件组。 ALTER DATABASE BOOK ADD FILE (

NAME = test1dat3,

FILENAME = '''+ @data_path + 't1dat3.ndf'', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ), (

NAME = test1dat4,

FILENAME = '''+ @data_path + 't1dat4.ndf'', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB )

TO FILEGROUP Test1FG1'

7

实验三：数据定义

一、实验目的

通过实验使学生掌握用不用的方法设计数据库表结构、修改数据库表结构以及删除表结构。

二、原理解析

1、用SQL Server 2005的SQL Server Management Studio设计数据库表结构

用SQL Server 2005的SQL Server Management Studio设计数据库表结构比较简单，只要在表设计器中对表中的各个字段进行设计，主要包括字段名、字段的数据类型等。

2、用T-SQL的CREATE TABLE语句创建表结构

CREATE TABLE语句的语法结构如下：

CREATE TABLE table_name

（{column_name datatype NOT NULL|NULL}）

3、用SQL Server 2005的SQL Server Management Studio设计数据库表结构

用SQL Server 2005的SQL Server Management Studio设计数据库表结构比较简单，与创建表结构类似，只要打开表设计器，在表设计器中修改并加以保存就可以了。

4、用T-SQL的CREATE TABLE语句创建表结构

CREATE TABLE语句的语法结构如下： ? 向表中添加列 ALTER TABLE 表名 ADD 列名 列的描述 ? 删除列

ALTER TABLE 表名 DROP COLUMN 列名 ? 修改列

ALTER TABLE 表名

ALTER COLUMN 列名 列的描述

5、删除表结构

? 使用SQL Server Management Studio删除表 ? 用T-SQL的DROP TABLE语句删除表

8

实验十：存储过程

一、实验目的

通过实验使学生加深对数据完整性的理解，学会创建和使用存储过程。 二、原理解析 1、存储过程概述

存储过程是指，在一个执行规划中预先定义并编译好的一组Transact-SQL语句。这些语句在一个名称下存储并作为一个单元进行处理。存储过程是编写数据库代码中的重要成份。它们可以是构成任何一个由数据库支撑的应用程序的代码，并且能被这些应用程序中的任何一个调用。

存储过程能在查询分析器（Query Analyzer）窗口运行。它们可以被批处理文件调用，也可以由Access、Visual Basic或者其他编程语言的用户接口程序来调用。可以像其他子例程调用一样，把参数传给存储过程。存储过程会返回数据值、状态代码以及错误信息，这样用户就可以做出适当的响应。存储过程也能调用其他存储过程。存储过程能够在本地计算机或远程服务器上运行。在执行系统内部任务时，用户可以指示SQL Server自动运行某种存储过程。

存储过程类型包括系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。通过存储过程的创建、执行、修改和删除操作，初步了解存储过程的类型，掌握利用存储过程进行程序设计的方法技巧。 2、存储过程的类型

系统存储过程、用户自定义存储过程、扩展存储过程。 3、在SQL中创建存储过程的语法 创建存储过程的语法为：

CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ]

29

AS sql_statement [ ...n ]

4、存储过程的执行 执行存储过程： [ [ EXEC [ UTE ] ] {

[ @return_status = ]

{ procedure_name [ ;number ] | @procedure_name_var }

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,...n ]

[ WITH RECOMPILE ] 5、存储过程中的参数

输入参数和输出参数（OUTPUT）。 三、实验内容

（1） 创建不带参数的存储过程。 （2） 创建带输入参数的存储过程。 （3） 创建带输出参数的存储过程。 四、实验步骤

1、在学生成绩库中创建存储过程，存储过程名为proc1，要求实现如下功能：根据学生学

号，查询该学生的选课情况，其中包括该学生学号、姓名、性别、课程号、课程名、成绩和学分等。

CREATE PROCEDURE proc1

@sno char(8) --@sno是参数，字符型，表示某个学生的学号 AS

SELECT xscj.学号, xsqk.姓名, xsqk.性别, xscj.课程号, xskc.课程名, xscj.成绩, xscj.学分 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 AND xsqk.学号 = @sno GO

30

2、现有一学生学号为“02020101”，要求通过调用上例中所建的名为proc1的存储过程，实现显示该学生的选课情况列表。 EXECUTE proc1 ?02020101? 或

EXECUTE proc1 @sno = ?02020101? 或

EXEC proc1 @sno =?02020101?

3、在学生成绩库中创建一个名为proc2的存储过程，产生一个学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、学分等，并调用该存储过程查看学生的选课情况。

CREATE PROCEDURE proc2 AS

SELECT xscj.学号, xsqk.姓名, xsqk.性别, xscj.课程号, xskc.课程名, xscj.成绩, xscj.学分 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 GO EXEC proc2

4、在学生成绩库中创建一个名为proc3的存储过程，产生某门课程的选课学生情况列表，其中包括课程号、课程名、学号、姓名、系别、班级、专业、性别等。要求输入某门课程的课程号，得到上述信息。 CREATE PROCEDURE proc3

@cno char (3) --@cno是参数，字符型，表示某门课的课程号 AS

SELECT xscj.课程号, xskc.课程名,xscj.学号, xsqk.姓名, xsqk.系别,xsqk.班级, xsqk.专业,xsqk.性别 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 AND xscj.课程号 = @cno

31

GO

5、利用上述存储过程查询课程号为“101”的课程的选课学生情况列表。 Exec Proc3 ?101?

6、定义存储过程proc4，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。

CREATE PROCEDURE proc4

@sno char(8) --@sno是参数，字符型，表示学生的学号 AS

DECLARE @savg tinyint --声明变量@savg，用于存放学生平均成绩 SELECT @savg = avg(成绩) FROM xscj

WHERE xscj.学号 = @sno --变量@savg存放学号为@sno的学生平均成绩 IF @savg>=60 --进行平均成绩的判断 PRINT ?此学生综合成绩合格！? ELSE

PRINT ?此学生综合成绩不合格！? GO

7、利用已创建的存储过程查询学号为“02020101”同学的平均成绩情况。 Exec proc4 ?02020101?

8、定义存储过程proc5，要求实现输入学号，输出该学生所选课程的平均成绩，要求用输入参数和输出参数实现。 CREATE PROCEDURE proc5

@sno char(10),@avgscore int OUTPUT AS

IF @sno=NULL

BEGIN

PRINT ?学号不能为空！? RETURN END

SELECT @avgscore=AVG(成绩)

32

from xscj

WHERE 学号=@sno RETURN

9、利用创建的存储过程查询学号为“02020101”的同学的平均成绩。 DECLARE @avgscore int

EXECUTE proc5 ?02020101?,@avgscore OUTPUT SELECT ?His average score is :?,@avgscore

10、定义存储过程proc6，要求实现如下功能：根据某学生的学号查询该生的已修学分，如果已修学分>9，则将已修学分加2分；如果已修学生小于9，则将已修学分加4分。 create proc proc6

@sno char(10),@xxf tinyint output as

declare @yxf tinyint

select @yxf=总学分from dbo.xsqk where 学号=@sno if @yxf>9 update dbo.xsqk set 总学分=总学分+2 where 学号=@sno else

update dbo.xsqk set 总学分=总学分+4 where 学号=@sno

select @xxf=总学分from dbo.xsqk where 学号=@sno

11、利用已经创建的存储过程查询学号为“02020101”的同学修改后的已修学分。 declare @xxf tinyint

exec proc6 '02020101',@xxf output select @Xxf

33

实验十一：触发器（1）

一、 实验目的

通过实验使学生加深对数据完整性的理解，学会创建触发器，会用触发器实现复杂的数据库完整性。

二、 原理解析 1、触发器概述

触发器是一种特殊类型的存储过程，用于保证数据完整性。事实上，它是一组SQL语句。当表中的一行被插入、修改或删除时，触发器被执行。

触发器与一般存储过程有一些区别，例如，存储过程可以直接调用，但是触发器不能被直接调用执行，只能由系统自动激活。触发器主要的用途是维护行级数据的完整性，而不是返回结果。 2、触发器的类型

插入INSERT、更新UPDATE、删除DELETE。 3、在SQL中创建触发器的语法 创建触发器的语法为：

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {

{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ]

[ NOT FOR REPLICATION ] AS

[ { IF UPDATE ( column )

[ { AND | OR } UPDATE ( column ) ] [ ...n ]

| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ]

34

sql_statement [ ...n ] } }

三、实验内容

（1）创建INSERT类型触发器。 （2）创建DELETE类型触发器。 （3）创建UPDATE类型触发器。 四、实验步骤

（1）在学生成绩库中创建触发器trigger1，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。

分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分基础上加上新选课程的学分就可以了。 USE StuScore --切换到学生成绩库 GO

CREATE TRIGGER trigger1 ON xscj --创建触发器trigger_9_1 FOR INSERT AS

--定义变量@xh，表示学号，定义变量@xf，表示学分 DECLARE @xh char(8),@xf tinyint

--从inserted表取出新插入的学生选课记录的学号与学分，分别赋给变量@xh和@xf

SELECT @xh=学号,@xf=学分 FROM inserted

--更新学生情况表中指定学号的学生的总学分，其中ISNULL为函数，用0替换原总学分中的空值 UPDATE xsqk

SET 总学分=ISNULL(总学分 , 0 )+@xf

35

WHERE 学号=@xh GO

（2）创建触发器trigger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。 USE StuScore GO

CREATE TRIGGER trigger2 ON xskc FOR UPDATE AS

PRINT '学生课程表被修改了' GO

（3）创建触发器trigger3，实现当删除学生课程表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。 USE StuScore GO

CREATE TRIGGER trigger3 ON xskc FOR DELETE AS

DELETE xscj FROM xscj,deleted WHERE xscj.课程号=deleted.课程号 GO

（4）创建触发器trigger4，实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。 USE StuScore GO

CREATE TRIGGER trigger4 ON xskc

36

FOR UPDATE AS

IF update(课程号) BEGIN UPDATE xscj

SET 课程号=(SELECT 课程号 FROM inserted) FROM xscj,deleted

WHERE xscj.课程号=deleted.课程号 END GO

（5）创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，查看该学生的信息是否存在在学生信息表中，如果不存在，则把该学生的基本信息加入到学生信息表中。 use stuscore go

create trigger trigger5 on xscj for insert as

declare @xh char(8)

select @xh=学号from inserted

if not exists(select * from dbo.xsqk where 学号=@xh) insert into dbo.xsqk(学号,姓名) values(@xh,'zhangsan') 测试：

insert into xscj values('30','301',89,2)

（6）创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，调用实验九中创建的存储过程proc5，实现统计该生的平均成绩功能。 存储过程的创建：

CREATE PROCEDURE proc5

37

@sno char(10),@avgscore int OUTPUT AS

IF @sno=NULL BEGIN

PRINT '学号不能为空！' RETURN END

SELECT @avgscore=AVG(成绩) from xscj

WHERE 学号=@sno RETURN

在触发器中调用存储过程： create trigger trigger6 on xscj for insert as begin

declare @xh char(8),@avgscore int select @xh=学号from inserted exec proc5 @xh,@avgscore output select @avgscore end

38

SELECT 库存数量 FROM 卷烟库存表

WHERE 卷烟品牌IN (SELECT 卷烟品牌FROM INSERTED) AND 库存数量<= 0 ) BEGIN --返回错误提示

RAISERROR('错误！该卷烟库存小于等于，不能销售。',16,1) --回滚事务 ROLLBACK RETURN END

（5）分别用触发器和存储过程实现对学生情况表（xsqk）和学生成绩表（xscj）表的级联删除。 存储过程：

create procedure p_del @sno varchar(8) as begin delete score where sno=@sno delete student where sno=@sno end 触发器：

create trigger tr_student_delete on xsqk for delete as

declare @num char(8)

44

select @num=学号from deleted delete xscj where 学号=@num go

（6）创建触发器8，要求实现：当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出“该学生已修满，不需要再选修！”的提示信息；否则，自动更新该学生的总学分。 create trigger tirgger8 on xscj for insert as

declare @sno char(8)

select @sno=学号from inserted if (select 总学分from xsqk where 学号=@sno)>=25 begin

raiserror('该学生已修满，不需要再选修!',16,1) rollback tran return end else begin

declare @xf tinyint

select @xf=学分from inserted update xsqk

set 总学分=总学分+@xf where 学号=@sno end

45

实验十三：安全性管理

一、 实验目的

通过实验使学生理解SQL Server 2000数据库安全性的管理方法。 二、 原理解析 1、SQL Server登录帐号

与登录有关的存储过程：

sp_addlogin 创建SQL Server登录帐号 sp_droplogin删除SQL Server登录帐号 sp_grantlogin创建windows登录帐号

sp_revokelogin删除windows用户或用户组在SQL Server上的登录信息 sp_denylogin拒绝某一windows用户或用户组连接到SQL Server 2、数据库用户帐号

只有数据库用户才具有访问、操作该数据库的权限，某一登录帐号要获得操作数据库的权限，必须与相应的数据库用户相映射。

与数据库用户帐号有关的存储过程： sp_grantdbaccess创建一个数据库用户 sp_revokedbaccess删除一个数据库用户 3、角色管理

可以把一些用户归入某一角色，这样只要角色具有了一定的权限，用户相应地从该角色继承了相应的权限，角色的提出有利于权限的管理。

角色可分为固定服务器角色和数据库角色。 与角色有关的存储过程：

sp_addsrvrolemember添加固定服务器角色成员 sp_addrole向数据库添加一角色 sp_addrolemember添加数据库角色成员 sp_dropsrvrolemember删除固定服务器角色成员 sp_droprolemember删除数据库角色成员 sp_droprole删除角色

注意：删除角色前，必须删除角色成员。

46

4、许可管理

许可是用来授权用户可以使用数据库中数据和执行数据库操作。 许可授予grant语句 许可禁止deny语句 许可收回revoke语句 三、 实验内容 （7） 登录帐号的管理。 （8） 数据库用户帐号的管理。 （9） 角色管理。 （10）许可管理。 四、 实验步骤

（1）创建SQL Server登录帐号aa，赋予其系统管理员角色。 sp_addlogin 'aa','123'

sp_addsrvrolemember 'aa','sysadmin'

（2）赋予windows登录帐号bb在数据库StuScore上的db_owner访问权限。 sp_grantlogin 'tjf\\bb' use stuscore go

sp_addrolemember 'db_owner','tjf\\bb'

（3）创建SQL Server登录帐号LoginT，其在StuScore数据库上的对应用户为userT。

sp_addlogin 'loginT','T' use stuscore go

sp_grantdbaccess 'loginT','userT'

（4）删除userT所对应的登录帐号loginT。 Sp_droplogin 'loginT'

（5）删除StuScore数据库用户帐号userT。 sp_revokedbaccess 'userT'

47

（6）在服务器上创建一Windows用户组gg，接着创建Windows用户cc，并使cc隶属于组gg，然后分别将gg和cc授权登录和访问SQL Server系统。依次利用此账号作测试登录。测试成功后运行脚本： sp_revokelogin 'cc'

然后以cc登录SQl Server系统，能否成功。运行脚本： sp_addlogin 'cc','cc' Go

sp_denylogin 'cc' Go

再以cc登录SQL Server系统，测试能否成功。

(7) 利用系统存储过程为数据库StuScore创建一数据库角色myrole，并创建一个数据库用户myuser1（对应登录名111），使其属于角色myrole。 sp_addrole myrole sp_addlogin '111','123'

sp_grantdbaccess '111','myuser1' sp_addrolemember 'myrole','myuser1'

(8) 将数据库用户myuser1添加为数据库StuScore的db_owner角色。 sp_addrolemember 'db_owner','myuser1'

(9) 在SQL Server查询分析器中，执行下列脚本，并查看数据库myTestDB。

--创建范例数据库myTestDB Create Database myTestDB Go

--创建测试用表stud01和stud02 use myTestDB Go

Create Table stud01 (

no char(5) , name char(10) ,

48

本文来源：https://www.bwwdw.com/article/lkbx.html