（一）账号
通过身份验证并不代表能够访问SQL Server中的数据，用户只有在获取访问数据库的权限之后，才能够对服务器上的数据库进行权限许可下的各种操作。
数据库用户账号可以从已经存在的Windows用户账号、Windows用户组、SQL Server的登录名或角色映射出来。
（二） 角色
角色是为了方便权限管理而设置的管理单位，角色是一种SQL Server安全账户。
角色包含SQL Server登录、Windows登录，若用户被加入到某一个角色中，则具有该角色的权限。
（三）权限（数据库权限管理）
1．对象权限
对象权限及其所作用的数据库对象如下。
①表对象，可执行的操作：SELECT、INSERT、UPDATE、DELETE、REFERANCES
②视图对象，可执行的操作：SELECT、INSERT、UPDATE、DELETE
③存储过程对象，可执行的操作：EXECUTE
④字段（列）对象，可执行的操作：SELECT、UPDATE
２.语句权限
语句权限决定用户能否执行以下语句。
①CREATE DATABASE语句，用于创建数据库。
②CREATE TABLE语句，用于在数据库中创建表。
③CREATE VIEW语句，用于在数据库中创建视图。
④CREATE DEFAULT语句，用于在数据库中创建默认对象。
⑤CREATE PROCEDURE语句，用于在数据库中创建存储过程。
⑥CREATE RULE语句，用于在数据库中创建规则。
⑦CREATE FUNCTION语句，用于在数据库中创建用户自定义函数。
⑧BACKUP DATABASE语句，用于备份数据库。
⑨BACKUP LOG语句，用于备份数据库日志。
3．隐含权限
隐含权限是指有些用户和角色不需要授权就有的权限，包括：固定服务器角色、固定数据库角色和数据库对象所有者所拥有的权限。
任务2 创建登录账号、用户、角色及权限管理
子任务 使用Windows账号
【例13-1】创建Windows账号MyLogin，并在Teaching数据库中创建一个同名用户，授予该用户查询S(学生表)数据的权限。
【练一练】创建一个Windows登录WinLogin,并使其具有查看和修改Product表数据的权限,并测试查看结果。
子任务 使用SQL Server账号
SQL Server账号的创建过程与Windows类似，主要通过命令方式完成。相关命令格式如下：
--创建SQL Server账号
CREATE LOGIN 登录名 WITH PASSWORD=’登录密码’
--创建数据库用户
CREATE USER 用户名
--给数据库用户授权
GRANT 权限 ON 表名 TO 用户名
--撤消用户权限
REVOKE 权限 ON 表名 TO 用户名
--禁止用户权限
DENY 权限 ON 表名 TO 用户名
--将用户添加到角色
SP_ADDROLEMEMBER 角色名，用户名
--将用户从角色删除
SP_DROPROLEMEMBER 角色名，用户名
--删除用户
DROP USER 角色名
--删除登录
DROP LOGIN 登录名
【例13-2】创建SQL Server账号MyLogin1，使其具有修改S(学生表)数据的权限。
【例13-3】撤消用户MyLogin1修改 S(学生表)数据的权限。
【例13-4】将MyLogin1添加到系统角色db_owner,使其具有Teaching数据库的所有权限。
示例：
【例１】用系统存储过程sp_grantlogin将Windows用户“SQL_HＸ”加入到SQL Server登录账户中。
EXEC sp_grantlogin ' MS-20170419NIDS\ SQL_HＸ'
或
EXEC sp_grantlogin [MS-20170419NIDS\ SQL_HＸ]
例：将用户' MS-20170419NIDS\Administrator'加入到SQL Server登录账户中。
EXEC sp_grantlogin ' MS-20170419NIDS\Administrator'
【例2】使用系统存储过程sp_addlongin创建一个'NEWHX123'登录账户，新登密码为'sqlserver_gzhxit', 默认数据库为教学管理。
EXEC sp_addlogin 'NEWHX123','sqlserver_gzhxit', 教学管理
[例3】用系统存储过程查看SQL Server系统中所有账户信息。
EXEC sp_helplogins
例：查看用户'NEWHX123'的账户信息。
EXEC sp_helplogins 'NEWHX123'
【例4】修改登录账户'NEWHX123'的密码。
EXEC sp_password 'sqlserver_gzhxit'','NEWsqlserver_gzhxit','NEWHX123'
【练一练】用命令创建一个SQL Server登录SQLLogin,创建同名用户并将其添加到Mydb数据库的角色db_owner，测试权限后将其从角色中移除，最后删除该用户和登录。
子任务 角色管理
角色是为了方便权限管理而设置的管理单位，角色是一种SQL Server安全账户。角色包含SQL Server登录、Windows登录，若用户被加入到某一个角色中，则具有该角色的权限。
（一）SQL Server角色的类型
1．固定角色
系统定义了固定角色，涉及服务器配置管理以及服务器和数据库的权限管理，固定角色分为固定服务器角色和固定数据库角色。
常用的固定服务器角色名称及权限如下。
①sysadmin：系统管理员，可以对SQL Server服务器执行任何操作。
②serveradmin：服务器管理员，具有对服务器设置和管理的权限。
③setupadmin：设置管理员，可添加和删除服务器的链接，并执行特定的系统存储过程。
④securityadmin：安全管理员，管理服务器登录标识、更改密码、创建数据库的权限，读取错误日志等。
⑤processadmin：进程管理员，管理在SQL Server服务器中运行的各个进程。
⑥dbcreator：数据库创建者，可创建、更改和删除数据库。
⑦diskadmin：管理系统磁盘文件。
⑧bulkadmin：在有INSERT权限的前提下，执行BULK INSERT语句。
固定数据库角色是指角色具有管理、访问数据库的权限已被SQL Server固定，并且SQL Server管理者不能对其进行任何更改。
常用的固定数据库角色的名称及权限如下。
①db_owner：数据库的所有者，具有数据库中的全部权限。
②db_accessadmin：数据库访问权限管理员，具有添加或删除用户（组）的权限。
③db_securityadmin：数据库角色管理员，具有管理角色和数据库角色成员、对象所有权、语句执行权限、数据库访问权限。
④db_ddladmin：数据库DDL管理员，在数据库中创建、删除或修改数据库对象。
⑤db_backupoperator：具有数据库备份权限。
⑥db_datareader：仅对数据库中的表执行SELECT操作，读取所有表的信息。
⑦db_datawriter：仅对数据库中的表执行INSERT、UPDATE、DELETE操作，但不能进行SELECT操作。
⑧db_denydatawriter：不能对表进行增、删、修改操作。
⑨db_denydatareader：不能读取数据库中表的内容。
⑩public：每个数据库用户都是public角色成员。
2．用户定义数据库角色
用户定义数据库角色的优点是SQL Server数据库角色，可以包含Windows用户组或用户；同一数据库的用户可以具有多个不同的用户定义角色，这种角色是自由组合，而不仅仅是public角色与其他角色的结合；角色可以进行嵌套，在数据库中实现不同级别的安全性。
（二）固定服务器角色管理
1．添加固定服务器角色成员
(1)用可视化方式添加固定服务器角色成员
【例】用可视化方式将登录账户“SQL_YH”添加为固定服务器角色dbcreator的成员。
用可视化方式添加固定服务器角色成员的具体操作步骤如下。
1)从“服务器角色”项添加
①在对象资源管理器中，展开“数据库服务器/安全性/服务器角色”选项，在右侧窗口中会显示当前数据库服务器的所有固定服务器角色。
②右击要添加成员的“dbcreator”固定服务器角色，在弹出的快捷菜单中选择“属性”命令，屏幕出现“服务器角色属性”窗口，如图8.8所示。
③用单鼠标单击窗口中“添加”按钮，屏幕会出现“选择登录名”对话框，单击其中的“浏览”按钮，在弹出的“查找对象”窗口中，如图8.9所示。选择要添加角色的登录账户，例如，选择“PC-200806201116\SQL_YH”，然后，单击“确定”按钮即可。
④在窗口中可以方便地单击“添加”和“删除”按钮实现对成员的添加和删除。
(2)用命令方式添加固定服务器角色成员
sp_addsrvrolemember[＠loginame=]’用户名’,[＠rolename=]’角色名’
说明：格式中各选项的含义如下。
①[＠loginame=]’用户名’表示添加到固定服务器角色的登录账户名称。
②[＠rolename=]’角色名’表示要加入的角色名称。
【例13】用系统存储过程将登录账户“SQL_YH”添加为固定服务器角色sysadmin的成员。
EXEC sp_addsrvrolemember ’SQL_YH’,’sysadmin’
【例】向学生管理数据库添加Windows用户“PC-200806201116\SQL_YH”。
USE 学生管理
GO
EXEC sp_grantdbaccess ’PC-200806201116\SQL_YH’, ’SQL_YH’
GO
EXEC sp_addrolemember ’db_ddladmin’ , ’SQL_YH’
【例】删除学生管理数据库角色中的“SQL_YH”用户。
USE 学生管理
GO
EXEC sp_droprole member ’db_owner’, ’SQL_YH ’
【例】用系统存储过程为学生管理数据库创建名为“YLH”数据库角色。
USE 学生管理
GO
EXEC sp_addrole ’YLH’
（五）应用程序角色管理
应用程序角色是用于控制应用程序存取数据，不包含任何成员。
在编写数据库的应用程序时，可以定义应用程序角色，让应用程序能使用编写的程序来存取SQL Server系统的数据，即应用程序的操作不需要在SQL Server系统拥有登录账号以及用户账号，但是仍然可以存取数据库。
1．应用程序角色与其他角色的区别
(1)应用程序角色没有成员，因为它们只在应用程序中使用，不需要那些用户权限。
(2)必须为应用程序角色设计一个激活密码。
2．使用应用程序角色的优点
(1)限制访问数据库所使用的应用程序，提高系统安全性。
(2)提高SQL Server服务器的运行性能。
3．建立应用程序角色
(1)用可视化方式建立应用程序角色
【例】设有一个“学生管理”程序，用可视化方式在学生管理数据库中建立应用程序角色：“学生管理-更新”，其口令为“abc”。
具体操作步骤如下。
①在对象资源管理器中，展开“学生管理/安全性”选项，右击其中的“角色”项，在弹出的快捷菜单上选择“新建应用程序角色”命令。
②在“应用程序角色-新建”窗口中，输入角色名称：“学生管理-更新”，默认架构选择“dbo”，输入密码：“abc”，如图16所示。
③单击“确定”按钮。在学生管理数据库中增加了“学生管理-更新”应用程序角色。
(2)用命令方式建立应用程序角色
sp_addapprole [＠ro1ename=]’角色名’,[＠password=]’密码’[,[＠encrypt=]’加密模式’]
【例】设有一个“学生管理”程序，用命令方式在学生管理数据库中建立应用程序角色：“学生管理-更新”，其口令为“abc”。
USE 学生管理
GO
EXEC sp_addapprole ’学生管理-更新’,’abc’
4．激活和使用应用程序角色
激活应用程序角色的语法格式如下。
sp_setapprole [＠ro1ename=]’角色名’,[＠password=]’密码’[,[＠encrypt=]’加密模式’]
【例】用命令方式激活已经创建的应用程序角色：“学生管理-更新”。
USE 学生管理
GO
EXEC sp_setapprole ’学生管理-更新’,’abc’,’none’
应用程序角色与标准角色之间有以下差别。
(1)应用程序角色没有成员，而标准角色可以有自己的成员。
(2)默认应用程序角色是无效的，只有当数据库应用程序用系统存储过程sp_approle激活它才能使用，而标准角色一直保持有效。
(3)应用程序角色激活后，其所拥有的访问权限才起作用。
(4)在运行应用程序所建立的连接断开，或删除应用程序角色后，其功能随之失去作用，标准角色中的访问功能恢复。
5．删除应用程序角色
(1)用可视化方式删除应用程序角色
【例】用可视化方式删除已建立的“学生管理-更新”应用程序角色。
在对象资源管理器中，展开“学生管理/安全性/角色”选项，右击其中的“学生管理-更新”应用程序角色，在弹出的快捷菜单上选择“删除”命令，屏幕会出现“删除对象”对话框。在其中选中要删除的应用程序角色，单击“确定”按钮即可完成删除操作。
(2)用命令方式删除应用程序角色
sp_dropappro1e [＠rolename=]’角色名’
【例28】用命令方式删除已建立的“学生管理-更新”应用程序角色。
EXEC sp_dropappro1e ’学生管理-更新’
数据库权限管理
（二）权限授予
n
1．用可视化方式完成用户或角色的权限授予
(1)用可视化方式完成用户或角色的语句权限授予
(2)用可视化方式完成用户或角色的对象权限授予
2．用命令方式完成用户或角色的权限授予
(1)用户或角色的语句权限授予
GRANT {ALL|语句权限列表} TO {PUBLIC|安全账号}
【例】授予用户“SQL_YH”具有创建表的权限。
USE 学生管理
GO
GRANT CREATE TABLE TO SQL_YH
GO
(2)用户或角色的对象权限授予
GRANT {ALL|对象权限列表}
ON {表[(列名表)]|视图[(列名表)]|存储过程|扩展存储过程|自定义函数}
TO {PUBLIC|用户账户列表}
[WITH GRANT OPTION]
[AS {组|角色}]
【例】授予角色“TYC”具有查询学生名册表、课程表、选课表的权限。
USE 学生管理
GO
GRANT SELECT ON 学生名册 TO TYC
GRANT SELECT ON 课程表 TO TYC
GRANT SELECT ON 选课表 TO TYC
3．查看权限
sp_helprotect[[＠name=]’名称’][,[＠username=]’查询的账户名’]
[,[＠grantorname=]’授权的账户名’][,[＠permissionarea=]’类型’]
【例】查询学生名册表的权限。
USE 学生管理
GO
EXEC sp_helprotect ’学生名册’
（二）权限的禁止与撤销
禁止权限就是删除以前授予用户、组或角色的权限，禁止从其他角色继承的权限，且确保用户、组或角色将来不继承更高级别的组或角色的权限。
撤销权限用于删除用户的权限，即是删除曾经授予的权限，并不禁止用户、组或角色通过别的方式继承权限。
1．禁止权限
(1)禁止语句权限
DENY {ALL|语句权限列表} TO {PUBLIC|安全账号}
(2)禁止对象权限
DENY {ALL|对象权限列表} ON {表[(列名表)] |视图[(列名表)]|存储过程|扩展存储过程|自定义函数}
TO {PUBLIC|用户账户列表}
[CASCADE ]
【例】禁止“SQL_YH”用户使用“CREATE VIEW”语句。
USE 学生管理
GO
DENY CREATE VIEW TO SQL_YH
2．撤销权限
(1)撤销语句权限
REVOKE {ALL|语句权限列表} FROM 安全账号
(2)撤销对象权限
REVOKE [GRANT OPTION FOR]{ALL|对象权限列表}
ON {表[(列名表)]|视图[(列名表)]|存储过程|扩展存储过程|自定义函数}
FROM {PUBLIC|用户账户列表}
[CASCADE]
[AS {组|角色}]
【例】撤销“SQL_YH”用户对创建表操作的权限。
USE 学生管理
GO
REVOKE CREATE TABLE FROM SQL_YH
小结：
身份验证是指当用户访问数据库时，系统对该用户的账号和口令的确认过程。SQL Server的身份验证包括3种模式：SQL Server身份验证模式、Windows身份验证模式、混合验证模式。
登录账户是用户建立自己与SQL Server的连接途径，可以使用SQL Server管理平台和T-SQL语句建立和删除登录账号。
角色是进行数据库权限管理的单位，分为固定角色和用户定义数据库角色两种。
权限是用户对数据库对象的使用及操作的权利，权限分为对象权限、语句权限和隐含权限3种。
