项目七 数据库维护.ppt

项目七数据库维护 2020 1 17 本项目内容 任务1 数据的迁移 任务2 事件探查器的应用 任务3 数据库认证模式和账户管理 当一个数据库被创建以后的工作都叫做数据库维护 数据库维护比数据库的创建和使用更难 数据库日常维护工作是系统管理员的重要职责 通过本项目的学习 读者应掌握以下内容 数据库分离和附加 数据库备份和恢复 掌握事件探查器的常用事件 掌握使用事件探查器进行跟踪的方法 数据库安全管理 数据库账户管理 任务1 数据的迁移 公司领导为保证日常工作的正常开展 要求对数据库进行备份 并将数据库中的数据导出到备份数据库中 一旦出现问题立即用备份数据库替换以维持日常工作 并进行原数据库的恢复 1 根据任务确定备份及恢复数据库的操作2 根据任务确定分离及附加数据库的操作 1 1分离和附加数据库1 1 1使用图形界面分离和附加数据库SQLServer允许分离数据库的数据和事务日志文件 然后将其附加到另一台服务器 甚至附加到同一台服务器上 分离数据库所做的操作是从SQLServer删除数据库 但却保持组成该数据库的数据和事务日志文件中的数据完好无损 然后将这些分离出来的数据和事务日志文件附加到任何SQLServer数据库上 从而使数据库的使用状态与分离前的状态完全相同 1 分离数据库 1 打开SQLServerManagementStudio 2 连接到服务器点确定 服务器类型和服务器名称不要动了 身份验证选择sqlserver身份验证 输入登录名 密码 如图7 1 图7 1登录数据库 3 登录成功后进入图7 2所示界面 选择health数据库点击右键 选择菜单 任务 分离 进入分离数据库对话框如图7 3所示 图7 2数据库服务器 4 在分离数据库对话框中 把打钩的选项都钩上后点击 确定 按钮 后面若出现选择对话框 则全选确定 图7 3分离数据库 5 这样就完成了数据库的分离工作 从文件夹中把health mdf以及health log ldf个文件通过复制粘贴备份起来 2 附加数据库 1 现在数据库服务器上已经没有health数据库了 利用health mdf和health log ldf附加数据库 右击 数据库 选择 附加 菜单 如下图7 4 图7 4数据库服务器 2 弹出附加数据库对话框 点击 添加 进入定位数据库文件对话框 如图7 5所示 选择数据库文件 图7 5附加数据库 3 在弹出的 定位数据库文件 对话框中 找到分离数据库后保留的文件health mdf 选择health mdf 单击确定 回到附加数据库对话框 如图7 6 图7 6定位数据库文件 4 在附加数据库对话框中出现了health mdf和health log ldf两个文件 点击 确定 如下图7 7 图7 7附加数据库 5 这时health数据库重新出现在服务器中 附加数据库完成 图7 8数据库服务器 1 1 2使用T SQL语句分离和附加数据库1 分离数据库语法格式 sp detach db dbname dbname skipchecks skipchecks 例1 使用语法分离health数据库 EXECsp detach db health 2 附加数据库语法格式 EXECsp attach db dbname dbname filename1 filename n 16 例2 使用语法附加health数据库 health mdf health log ldf文件放在D盘根目录 EXECsp detach dbhealth d health mdf d health log ldf 1 2备份和恢复数据库SQLServer的备份和恢复功能为存储在SQLServer数据库中的关键数据提供了重要的保护手段 数据库备份就是制作数据库的副本 以在数据库遭到破坏时能够修复数据库 即进行数据库恢复 数据库恢复就是把数据库从错误状态恢复到某一正确状态 当数据库出现故障时 将备份的数据库加载到系统 从而使数据库恢复到备份时的正确状态 数据库的恢复是以备份为基础的 备份和恢复数据库也可以用于其它目的 如可以通过备份和恢复将数据库从一个服务器移动或复制到另一个服务器 1 完全数据库备份 2 事务日志备份 3 差异备份 4 文件或文件组备份 1 数据备份的类型 SQLServer有四种备份类型 1 2 1使用图形界面备份和恢复数据库1 备份数据库 1 选中要备份的数据库 单击右键 选择 备份 菜单 如图7 9 图7 9数据库服务器 2 在备份数据库对话框中 选择数据库备份到的路径 C ProgramFiles MicrosoftSQLServer MSSQL 4 MSSQL Backup 数据库备份文件名为UFDATA 999 2011 bak 点击确定 图7 10备份数据库 3 备份成功 弹出如下对话框 图7 11备份成功 2 数据库恢复 1 在另外一台电脑的数据库中 可以用刚才做的数据库备份文件 C ProgramFiles MicrosoftSQLServer MSSQL 4 MSSQL Backup UFDATA 999 2011 bak 还原该数据库 在数据库上单击右键 选择 还原数据库 菜单 如下图7 12 2 弹出还原数据库界面 给目标数据库取名为 UFDATA 999 2011 单击 源设备 按钮 找出备份文件 如图7 13 图7 13还原数据库 3 进入指定备份对话框如图7 14选择备份媒体 类型为 文件 点击 添加 按钮进入图7 14定位备份文件 选择用来进行数据库恢复的文件 图7 14指定备份 4 选择相关备份文件 点击 确定 图7 15定位备份文件 5 系统返回还原数据库对话框 备份集中出现以下备份集 点击 确定 图7 16还原数据库 6 显示还原成功对话框 这时数据库中出现UFDATA 999 2011数据库 图7 17还原成功 1 2 2使用T SQL语句备份和恢复数据库 1 数据库备份数据库备份语法 BACKUPDATABASE数据库名TODISK 物理磁盘文件 2 数据库还原数据库还原语法 RESTOREDATABASEdatabase nameFROMdisk 物理磁盘文件名 例3 将UFDATA 999 2011数据库备份到d盘data文件夹中 BACKUPDATABASEUFDATA 999 2011TODISK d data erp bak 例4 根据文件 d data erp bak 还原数据库UFDATA 999 2011 RESTOREDATABASEUFDATA 999 2011FROMdisk d test erp bak 1 3导入和导出数据表如果建立一个数据库时 想将分散在各处的不同类型的数据库分类汇总在这个新建的数据库中时 尤其是在进行数据检验 净化和转换时 将会面临很大的挑战 幸好SQLServer为我们提供了强大 丰富的数据导入导出功能 并且在导入导出的同时可以对数据进行灵活的处理 下面介绍使用数据转换服务 DTS 对数据进行导入和导出 例5 把客户方在Excel模版中准备的基础资料档案 如 Warehouse仓库档案表 导入到数据库UFDATA 999 2011 1 启动SQLSeverManagementStudio工具 在 对象资源管理器 中展开 数据库 树形目录 右击UFDATA 999 2011数据库 在弹出的快捷菜单中选择 任务 导入数据 命令 如图7 18所示 弹出 SQLSever导入和导出向导 对话框 单击 下一步 按钮 如图7 19所示 图7 18导入数据 图7 19导入和导出向导 2 单击图7 20所示的 数据源 对话框 excel导出 在 数据院 下拉列表中选择Microsoftexcel 在 excel文件路径 文本框中输入目标数据的文件名和路径 这里为I 仓库档案表 xls 图7 20选择数据源 3 单击图7 20所示的 下一步 按钮 单击图7 18所示的 选择目标 对话框 excel导入 在 目标 下拉列表框中选择MicrosoftOLEDBPROVIDERFORSQLSEVER 在 服务器名称 下拉列表框中选择或输入服务器的名称 图7 21选择目标程序 4 单击图7 21所示的 下一步 按钮 单击图7 22所示的 指定表复制或查询 对话框 excel导入 图7 22指定表复制或查询 5 单击图7 22所示的 下一步 按钮 弹出如图7 23所示的 选择源表和源视图 对话框 excel导入 在图7 23中列出了源数据库中所包含的表 可以选择一个或多个表作为源表 图7 23选择源表和源视图 6 单击7 23所示的 完成 按钮 弹出如图7 24所示的 保存并执行包 对话框 excel导入 选中 立即执行 复选框 图7 24保存并执行包 7 单击图7 24所示的 下一步 按钮 弹出如图7 25所示的 完成该导向 对话框 excel导入 图7 25完成向导 8 单击7 25所示的 完成 按钮 开始执行数据导入操作 如图7 26所示 图7 26执行成功 例6 把数据库UFDATA 999 2008中的customer 客户表 导出到UFDATA 999 2011数据库中 1 启动SQLSeverManagementStudio工具 在 对象资源管理器 中展开 数据库 树形目录 右击UFDATA 999 2008数据库 在弹出的快捷菜单中选择 任务 导出数据 命令 如图7 24所示 弹出 SQLSever导入和导出向导 对话框 如图7 25所示 图7 27导出数据 图7 28导入和导出向导 2 单击图7 28所示中 下一步 按钮 在 选择数据源 对话框 excel导出 的 数据源 下拉列表框中选择MicrosoftOLEPROVIDERFORSQLSEVER 在 服务器名称 下拉列表框中选择或输入服务器名称 身份验证可以选择 使用windows身份验证 模式 也可以选择 使用SQLSever身份验证 模式 如果选择了后一种方式 还需要在 用户名 文本框中输入登入时使用的用户帐户名称 然后在 加密 文本框中输入登入密码 如图7 29所示 图7 29选择数据源 3 单击图7 29所示的 下一步 按钮 弹出如图7 30所示的 选择目标 对话框 在 目标 下拉列表中选择目标数据库的格式为MicrosoftOLEPROVIDERFORSQLSEVER 在 服务器名称 下拉列表框中选择或输入服务器名称 数据库选择目标数据库 图7 30选择目标 4 单击图7 30所示的 下一步 按钮 单击图7 31所示的 指定表复制或查询 对话框 图7 31指定表复制或查询 5 单击图7 31所示的 下一步 按钮 弹出如图7 32所示的 选择源表和源视图 对话框 这里选择客户表Customer 图7 32选择源表和源视图 6 单击图7 32所示的 下一步 按钮 弹出如图7 33所示的 保持并执行包 对话框 excel导出 选中 立即执行 复选框 图7 33保存并执行包 7 单击图7 33所的 下一步 按钮 弹出如图7 34所示的 完成该向导 对话框 图7 34完成向导 8 单击7 34所示的 完成 按钮 开始执行数据导出操作 最后出现如图7 35所示的对话框 图7 35执行成功 1 4数据复制对于一个地域分散的大型企业组织来说 构建具有典型的分布式计算机特征的大型企业管理信息系统时 总要解决一个很重要的问题 如何在多个不同数据库服务器之间保证共享数据的一致性 之所以有这个重要的问题在于企业组织在不同地点对具有相同结构的本地数据库进行修改 但要保证修改后的数据库有相同的结果 其本质就是在对本地数据库的修改体现在其他具有相同数据的远程数据库中 那么如何解决这个问题就要用到数据库的复制技术 SQLSERVER提供了内置的复制能力 复制组件并不是附加产品 而是核心引擎的一部分 在复制这个技术的支持下 用户可以跨局域网 广域网或因特网的不同数据服务器上维护数据的多个拷贝 从而保证数据的一致性 复制模型 1 出版物和论文 2 出版者 3 分发者 4 订阅者 5 订阅类型 2 复制类型SQLSEVER提供了三大类复制类型 快照复制 事务复制 合并复制 可以在实际应用中使用相应的复制类型 每一种复制类型都在不同程序上实现数据的一致性 1 快照复制 2 事务复制 3 合并复制 无论是快照复制 事务性复制还是合并复制 创建复制都要经过以下几个步骤 1 创建发布服务器 选择要发布的服务器 如果有条件的 也可以分发服务器 在这里我们就将发布服务器和分发服务器设置在同一台计算机上 2 不论是发布服务器还是订阅服务器必须开启代理服务 3 创建一个发布 即将需要的数据库及对象发布出来 4 选择一个适合自己的发布类型 5 设置复制代理及安全 即指定可以运行代理的用户帐号 6 创建可以使用此发布的订阅服务器 3 配置复制 例7 现要求把UFDATA 999 2008数据库中的数据 如 科目档案表Code和存货档案表Inventory 复制到UFDATA 999 2011数据库中 实现数据的同步 1 创建发布服务器 1 启动SQLSeverManagementStudio工具 在 对象资源管理器 中展开 复制 本地发布 右击 本地发布 在弹出的快捷菜单中选择 新建发布 如图7 33所示 弹出 新建发布向导 对话框 如图7 34所示 图7 36新建发布 图7 37新建发布向导 2 点击图7 37所示的 下一步 按钮 弹出如图7 38所示的第一个选项 图7 38分发服务器 3 点击图7 38所示的 下一步 按钮 弹出如图7 39所示的第一个选项 图7 39启动SQLServer代理 4 点击图7 39所示的 下一步 按钮 弹出如图7 40所示 指定快照文件夹 此文件夹用于存储复制过程中产生的快照文件 图7 40指定快照文件夹 5 点击图7 40所示的 下一步 按钮 弹出如图7 41所示中要发布的数据库 这里选择UFDATA 999 2008数据库 图7 41选择发布数据库 6 点击图7 41所示的 下一步 按钮 弹出如图7 42所示中的发布类型 这里选择发布类型为 快照发布 图7 42发布类型 7 点击图7 42所示的 下一步 按钮 弹出如图7 43所示中的项目 选择要发布的对象 这里选择科目表Code和存货档案表Inventory 图7 43项目 8 点击图7 43所示的 下一步 按钮 弹出如图7 44所示中的筛选表行 这里不进行筛选 图7 44添加筛选表行 9 点击图7 44所示的 下一步 按钮 弹出如图7 45所示中的第一个选项 图7 45快照代理 10 点击图7 45所示的 下一步 按钮 弹出如图7 46所示中的 创建发布 图7 46向导操作 11 点击图7 46所示的 下一步 按钮 弹出如图7 47所示 在 发布名称 中取个名称 这里取的发布名称为 UFDATA发布 图7 47完成发布向导 12 点击图7 47所示的 完成 按钮 弹出如图7 48所示 显示全部成功 点击 关闭 按钮 图7 48正在创建发布 13 展开 复制 本地发布 就可以看见创建好的UFDATA发布 图7 49已创建好的UFDATA发布 2 创建订阅服务器 1 启动SQLSeverManagementStudio工具 在 对象资源管理器 中展开 复制 本地订阅 右击 本地订阅 在弹出的快捷菜单中选择 新建订阅 如图7 50所示 弹出 新建订阅向导 对话框 如图7 51所示 图7 50新建订阅 图7 51新建发布向导 2 点击图7 51所示的 下一步 按钮 弹出如图7 52所示 选择发布服务器 数据库和发布 图7 51订阅发布 3 点击图7 51所示的 下一步 按钮 弹出如图7 52所示中的第二个选项 请求订阅 图7 52分布代理位置 4 点击图7 52所示的 下一步 按钮 弹出如图7 53所示中的订阅服务器和订阅数据库 这里订阅服务器选择Computer 订阅数据库为UFDATA 999 2011 5 点击图7 53所示的 下一步 按钮 弹出如图7 54所示 点击 按钮 分别选择模拟进程帐户 图7 54分发代理安全性 6 点击图7 54所示的 下一步 按钮 弹出如图7 55所示 在 代理计划 中选择 连续允许 图7 55代理的同步计划 7 点击图7 55所示的 下一步 按钮 弹出如图7 56所示 在 初始化时间 中选择 立即 图7 56初始化订阅 8 点击图7 56所示的 下一步 按钮 弹出如图7 57所示中的 创建订阅 图7 57向导操作 9 点击图7 57所示的 下一步 按钮 弹出如图7 58所示的订阅相关信息 图7 58完成向导 10 点击图7 58所示的 完成 按钮 弹出如图7 59所示的成功信息 点击 关闭 按钮 图7 59正在创建订阅 11 展开 复制 本地订阅 就可以看见创建好的本地订阅 如图7 60所示 图7 60已创建好的订阅 3 测试配置好的复制 1 在UFDATA 999 2008数据库中的Code表中插入一条数据 如图7 61所示 图7 61向Code表中插入一条数据 2 在UFDATA 999 2011数据库中的Code表中可以看到同步复制过来的那条数据 如图7 62所示 图7 62同步复制过来的数据 BACKUPDATABASEUFDATA 999 2011TODISK d data erp bak RESTOREDATABASEUFDATA 999 2011FROMdisk d test erp bak EXECsp detach db UFDATA 999 2011 EXECsp attach db UFDATA 999 2011 d erp mdf d erp ldf 本任务的相关知识主要包括数据库的备份与恢复以及数据的分离与附加 分别通过用SQLsever2005Managemengstudio界面操作与T SQL语句来完成 任务2 事件探查器的应用 U8操作员月末对库存商品进行记账 由于网络环境等原因造成记账的过程中出现错误 对采购入库单记完账后 再对材料出库单进行记账时查询不到可记账单据 导致了不可以进行记账处理 1 进行 正常单据记账 后过滤不出单据 导致记不了账 通过单据列表里查看材料出库列表记账情况 发现材料出库单 记账人 前多了字符 IA ASSUSER 而 记账人 应该是 demo 2 因此 打开 材料出库列表 界面的同时通过事件探查器跟踪 记账人 涉及到的数据表 确定问题所存在的表后 把跟踪到的语句复制到查询分析器中 把出现问题的字段 记账人 进行清空 然后再返回到 正常单据记账列表 进行记账 2 1事件探查器工具的概述本任务主要介绍在实际维护工作中经常用到的一个SQLServer工具 事件探查器 SQL事件探查器是SQLServer提供的一个图形工具 通过该工具主要可以实现如下功能 1 查找执行最差的查询 2 监视存储过程的性能 3 审核Microsoft SQLServer 活动 4 监视每个用户的Transact SQL活动 5 分析程序业务流程和数据库内数据流向 6 定位出错语句 2 2事件探查器常用事件1 常用事件分类 2 常用事件类由事件生成的所有数据显示在单个行中的跟踪内 该行包含详细描述事件的数据列 称为事件类 2 存储过程事件类存储过程事件类是执行存储过程所产生的事件的集合 也是一个常用的事件类 其对应的常用事件解释如表2 4所示 3 错误和警告事件类错误和警告是在发生SQLServer错误或警告时产生的事件类集合 例如 一个在编译存储过程中产生的错误 或者一个SQLServer中的异常错误 其对应的常用事件类解释如下表2 5所示 2 3事件探查器的实际应用在实际U8软件维护过程中 经常遇到的问题主要有两类 一类是显式的错误和异常 可直接在事件探查器中利用Exception事件捕获 另一类是隐式的数据错误 在事件探查器中无明显的问题捕获标识 如用户的汇总表和明细表数据不一致 通常叫做数据对不上 等 1 创建跟踪的方法 1 从 开始 所有程序 SQLServer节点下打开事件探查器 SQLServerProfile 单击 文件 新建跟踪 如图7 58所示 连接对应的服务器 点击 确定 如图7 59所示 2 在 跟踪名 中输入对应的名称 默认为 无标题 数字 在使用模板中也可以选择已有的跟踪模板 如图7 60所示 图7 60跟踪属性窗口 4 在 事件选择 页选择您希望跟踪的事件 如果想显示所有的事件则在 显示所有事件 前打勾 建议选择 TSQL SQL BatchCompleted 存储过程 SP StmtCompleted 错误和警告 ErrorLog EventLog Exception 如果想显示所有的数据列则在 显示所有列 前打勾 建议选择 EventClass TextData SPID 如图7 61所示 图7 61事件和数据列选择窗口 5 选择完毕后点 运行 开始跟踪 如图7 62所示 图7 62运行的跟踪界面 2 显式的错误和异常的解决方法对于第一类问题 通常的解决办法是 首先要利用事件探查器跟踪有问题账套 重点查看在跟踪过程中 报错语句前后的sql语句所涉及到的数据表或数据对象内容是否有问题 确定问题账套的报错位置后 再利用事件探查器跟踪演示账套 或者不出问题的其他用户账套 并且重点对比有问题账套报错处前后sql语句涉及的表或数据对象内容有何差别 根据对比结果确定出错数据 对问题账套进行修改即可 在实际处理问题过程中 应用这种办法通常都能有效解决此类问题 3 隐式的数据错误问题的解决方法对于这类问题 通常的解决办法是 再现该存在问题的操作 在软件界面操作 同时实时查看后台的跟踪结果 查找出实现该功能sql语句所操作的数据表或数据对象 在实际处理问题过程中 通常遇到的情况都是 存在问题操作涉及的明细表数据正确 而汇总表数据有错 这时 利用明细表中数据来修改汇总表中的对应数据即可 1 点击 正常单据记账 后无可记账单据 如图7 63所示 在单据列表里查看单据记账情况 发现材料出库单记账人前多了字符 IA ASSUSER 而记账人应该是 demo 如图7 64所示 图7 63正常单据记账 图7 64材料出库单列表 2 通过启动事件探查器 SQLServerProfile 进行跟踪 查看在跟踪过程中 报错语句前后的sql语句所涉及到的数据表 如图7 65所示 图7 65事件探查器的跟踪结果 3 通过跟踪找了出现问题的所涉及到的数据表 然后把跟踪到的SQL语句进行复制到查询分析器进行执行 记账人应该是 demo 记账人前多了字符 IA ASSUSER 如下图7 66所示 图7 66查询结果 4 在查询分析器中通过下列语句把记账人清空掉 updaterdrecordssetcbaccounter nullwhereidin selectidfromrdrecordwherebrdflag 0 andccode 0000000001 andcvouchtype 11 5 重新进入节点 正常单据记账 后进入正常单据记账列表就过滤出来了两张单据 再点击 记账 就成功完成了记账功能 如图7 67所示 图7 67正常单据记账列表 本任务针对在软件维护过程出现的问题 通过事件探查器 SQLServerProfile 进行跟踪出错误所涉及到的数据表或数据对象 在数据库中解决问题 重点掌握事件探查器 SQLServerProfile 的常用事件 创建跟踪的方法以及一些错误的解决方法 任务3 数据库认证模式和账户管理 为保护数据安全性 同时维持正常工作 公司领导要求为仓库部门的普通员工创建用户账号 该用户账号可以登录数据库并完成数据的查询 新增工作 1 根据任务确定创建登录账号2 将登录账号映射为数据库用户账号3 为该数据库用户账号分配查询表 新增数据的权限 3 1安全认证模式SQLServer的安全机制可分为三个等级 SQLServer的登录安全性 数据库的访问安全性 数据库对象的使用安全性 每个安全等级就好象一道门 如果用户拥有开门的钥匙 则用户可以通过每一道门到达下一个安全等级 如果通过了所有的门 则用户就可以实现对数据的访问 为了实现安全性 每个网络用户在访问SQLServer数据库之前 都必须经过两个阶段的检验 身份验证阶段 用户在SQLServer上获得对任何数据库的访问权限之前 必须登录到SQLServer上 并且被认为是合法的 SQLServer或者操作系统对用户进行验证 权限验证阶段 用户身份验证通过后 登录到SQLServer上 系统检查用户是否具有访问服务器上数据对象的权限 3 1 1身份验证SQLServer有两种身份认证模式 Windows认证模式和SQLServer认证模式1 Windows身份验证模式 2 混合模式 3 1 2权限认证通过了认证并不代表用户就能访问SQLServer中的数据 为防止不合理的使用造成数据的泄密和破坏 SQLServer2005数据库管理系统除使用身份验证方法来限制用户进入数据库系统外 还使用权限认证来控制用户对数据库的操作 当用户身份通过验证连接到SQLServer2005服务器后 在用户可以访问的每个数据库中都要求单独的用户账号 对于没有账户的数据库 将无法访问 3 2用户管理3 2 1登录用户管理登录用户即为用户创建的登录帐号 可以访连接到SQLServer数据库的帐号 使用T SQL创建登录账号 语法格式如下 sp addlogin loginame login passwd password defdb database 例5 建立一个名为UFDATA 999 2011 login的登录账号 密码为123456789 EXECsp addlogin UFDATA 999 2011 login 123456789 UFDATA 999 2011 删除登录账号 语法格式如下 sp droplogin loginame login 例6 删除名为UFDATA 999 2011 login的登录账号 EXECsp droplogin UFDATA 999 2011 login 3 2 2数据库用户管理数据库用户帐号唯一标识一个用户 在数据库中 一个用户或工作组取得合法的登录帐号 只表明该帐号通过了WindowsNT认证或者SQLServer认证 但不能表明其可以对数据库数据和数据库对象进行某种或者某些操作 只有当他同时拥有了数据库用户帐号后 才能够访问数据库 在一个数据库中 用户帐号唯一标识一个用户 用户对数据库的访问权限以及对数据库对象的所有关系都是通过数据库用户帐号来控制的 用户帐号总是基于数据库的 即两个不同的数据库可以有两个相同的用户帐号 并且一个登录帐号也总是与一个或多个数据库用户帐号相对应的 如 登录帐号sa自动与每一个数据库用户dbo相关联 通常而言 数据库用户帐号总是与某一登录帐号相关联 数据库的安全性主要是靠管理数据库用户账号来控制的 1 创建数据库用户 1 使用sp grantdbaccess添加数据库用户 语法格式如下 EXECUTEsp grantdbaccess 登录名 用户名 2 使用sp adduser添加数据库用户 语法格式如下 EXECUTEsp adduser loginame login name in db user grpname group 例7 使用sp grantdbaccess和sp adduser 为登录账户 UFDATA 999 2011 login 在数据库UFDATA 999 2011中创建数据库用户 test EXECUTEsp grantdbaccess UFDATA 999 2011 login test EXECsp adduser UFDATA 999 2011 login test 2 删除数据库用户EXECUTEsp revokedbaccess name in db user 例8 使用SQL语句 删除用户 test EXECUTEsp revokedbaccess test 3 3角色管理SQLServer管理者可以将某些用户设置为某一角色 这样只对角色进行权限设置便可实现对所有用户权限的设置 大大减少了管理员的工作量 在SQLServer中主要有两种角色类型 服务器角色与数据库角色 3 3 1服务器角色管理 服务器角色是指根据SQLServer的管理任务 以及这些任务相对的重要性等级来把具有SQLServer管理职能的用户划分成不同的用户组 每一组所具有管理SQLServer的权限已被预定义 服务器角色适用在服务器范围内 并且其权限不能被修改 例如 具有sysadmin角色的用户在SQLServer中可以执行任何管理性的工作 任何企图对其权限进行修改的操作都将会失败 SQLServer共有8种预定义的服务器角色 各种角色的具体含义如下表所示 3 3 2数据库角色管理当多个用户需要在某个特定的数据库中执行类似的动作时 可以向该数据库中添加一个角色 数据库角色代表了可以访问相同数据库对象的一组数据库用户 数据库范围内分为三种角色 固定数据库角色 public角色 用户定义的数据库角色 1 固定数据库角色SQLServer固定数据库角色的具体含义如下表所示 2 public角色 数据库中的每个用户都属于public数据库角色 如果想让数据库中的每个用户都能有某个特定的权限 则将该权限指派给public角色 如果没有给用户专门授予对某个对象的权限 他们就使用指派给public角色的权限 3 用户定义的数据库角色 1 创建角色 在当前数据库中创建新角色 其语法格式 sp addrole role owner 例9 在UFDATA 999 2011数据库中创建一个名称为myrole的角色 EXECsp droprole myrole 2 删除角色 使用sp droprole从当前数据库删除指定的角色 其语法为 sp droprole role 例10 删除UFDATA 999 2011数据库中的myrole角色 EXECsp addrole myrole dbo 3 为用户添加角色 要把一个数据库用户添加到一个角色中 应使用sp addrolemember存储过程 其语法为 EXECUTEsp addrolememberrole name user name 4 取消用户角色 取消数据库用户拥有的角色 使用sp droprolemember存储过程 其语法为 EXECUTEsp droprolememberrole name user name 例11 使用SQL语句 为数据库用户 test 指定固定的数据库角色db accessadmin 完成后再取消该角色 EXECUTEsp addrolemember db accessadmin test EXECUTEsp droprolemember db accessadmin test 3 4权限管理权限管理是SQLServe安全管理的最后一关 访问权限指明用户可以获得哪些数据库对象的使用权 以及用户能够对这些对象执行何种操作 将一个登录名映射为一个用户名 并将用户名添加到某种数据库角色中 其实都是为了对数据库的访问权限进行设置 以便让各用户能够进行适合其工作职能的操作 3 4 1授予权限把权限授予某一用户 允许用户对数据库对象执行操作 即允许某个用户或角色 对一个对象执行某种操作或语句 1 使用图形界面授予权限打开ManagementStudio 选中Code表 点击右键 选择 属性 出现Code表属性对话框 此时 用户或角色 栏中为空 点击 添加 按钮 出现 选择用户或角色 对话框 点击 浏览 选择要授予权限的用户 出现 查找对象 对话框 选择test用户 点击 确定 返回表属性对话框 在表属性对话框中 用户或角色 一栏中出现了test用户 在 test显示权限 一栏中选select Update insert权限 授予test用户 2 使用T SQL语句授予权限语法格式 GRANT ALL 语句名称 n TO用户 角色 n 例12 在UFDATA 999 2011数据库中 授予用户test对科目表Code的查询 插入和更新的权限 GRANTSELECT INSERT UPDATEONCodeTOtest 3 4 2禁止权限禁止用户对某一对象或语句的权限 即拒绝某个用户或角色对一个对象执行某种操作 即使该用户或角色曾经被授予了这种操作的权限 或者由于继承而获得了这种权限 仍然不允许执行相应的操作 1 使用图形界面禁止权限打开图形界面 选中Code表 点击右键 选择 属性 出现Code表属性对话框 此时 用户或角色 栏中为test用户 在 test显示权限 一栏中把select Update insert权限 改为拒绝 以禁止授予test用户的这三个权限 2 使用T SQL语句禁止权限语法格式 DENY ALL 语句名称 n TO用户 角色 n 例13 在UFDATA 999 2011数据库中 禁止用户test对科目表Code的查询