许多Windows系统管理员，还兼职着微软SQL Server数据库管理员（DBA）的身份。另一方面，企业将许多机密的信息存储到了SQL Server数据库中。作为一名DBA新手，则需要了解SQL Server的安全模式和如何配置其安全设置，以保证“合法”用户的访问并阻止“非法”访问。而在SQL Server中登陆、用户、角色、权限提供了对数据库访问的权限，接下来在数据库安全性上着重分析它们的关系。
● 安全层次和验证模式
一、安全层次
SQL Server支持三级安全层次。在我们登陆到SQL Server时，其实我们是经过了三步的验证。
第一层次是用户提供正确的账号和密码登录到SQL Server，或者已经成功登陆了一个可以映射到SQL Server的windows账号。但是在SQL Server登陆并不意味着能够访问数据库，而是要经过第二层次的验证。
第二层次的权限允许用户与一个或多个数据库相连，这一层次的实现要在数据库对象的用户中绑定登陆账户。
第三个层次的安全权限允许用户拥有对指定数据库中的对象的访问权限，例如：可以指定用户有权使用哪些表和视图、运行哪些存储过程。在第一层次中的windows账号，其实是在我们装机时给windows指定的自己登陆到windows系统的账号，而作为windows系统管理员的我们其实也兼职了SQL Server的管理权，那我们如何设置才能保证只有我们指定的用户才能访问SQL数据库呢？就是我们下面要说的验证模式。
二、SQL登陆验证模式
SQL的登陆验证模式有两种，一种是Windows的验证模式，另一种是Windows和SQL Server混合验证模式。如果我们选择windows模式登陆并把windows账号映射到SQL Server的登陆上，那么合法的windows用户也就连接到了SQL Server中。
Windows模式的登陆需要在SQL Server中设置。方法：
打开SQL Server企业管理器，找到安全性文件夹，打开后再登陆中新建一个windows身份验证模式的账户。需要注意的是在新建账户时，账户的名称一定要填我们windows账户的名字，如：我的计算机在用户中名称为张信秀，则在填名称时一定要填张信秀。
SQL Server的验证模式相对windows的登陆模式在设置上没有特别的要求，只需填上我们的密码即可。
接下来进入我们的重点——角色、权限，首先我们在图上来区分。
● 登陆、权限、角色
在往下说之前我们先介绍下安全账户。返回到安全层次上来说，账户在登录到SQL Server后，如果想获得访问某个数据库的权限，是必须要在SQL中获得安全账户的，确保登陆的账号是安全的，就好比是我们出国的护照签证一样，想要去哪个国家必须获得该国家的签证和出入境章，这个安全账户就是在数据库对象的用户中绑定一个登陆账号，证明这个登陆账号是安全的。
账户、角色、权限他们三者是没有严格的关系的，如果有的话就是角色给账户分配职能，而权限却又限制着角色和账户对数据库的操作。它们三者就好像是公司里的员工、职权、规章，公司按照职能非配不同的员工，但是每个员工却又受到公司规章的限制。
一、数据库角色
数据库角色控制着数据库的安全性。当最终用户成功地连接到分析服务器之后，会在那个服务器上的数据库角色中查找最终用户的用户名，来确定用户对数据库的可能操作。
数据库角色在创建时有两种：
1、标准角色：不允许嵌套，在使用时只需在安全账户中添加，通过分配权限确保了用户能够进行的操作；
2、应用程序角色：为了让标准角色嵌套，来控制角色所能进行的操作，创建后SQL Server把它当做账户看待（因为它是应用程序级别的），它的作用是为权限提供相应的加密，用存储过程sp_setapprole激活才可进行授予权限的操作。
二、权限
权限有三种类型，
1、语句权限：限制对表update、insert等的操作，在创建角色时或添加安全用户后设置；
2、对象权限：限制用户对数据库对象的创建，在数据库属性内设置；
3、暗示性权限：SQL Server数据库自带的那些角色或用户，如：服务器角色、数据库所有者（dbo）等拥有的权限，不需要了解他，因为它是系统自己设置的用户不能够设置。
最后我们在代码中应用下三者：
/*示例说明：在数据库company中创建一个拥有表product的所有权限、拥有表employees的SELECT权限的角色rtest随后创建了一个登录ltest，然后在数据库company中为登录ltest创建了用户账户utest同时将用户账户utest添加到角色rtest中，使其通过权限继承获取了与角色rtest一样的权限最后使用DENY语句拒绝了用户账户utest对表titles的SELECT权限。经过这样的处理，使用ltest登录SQL Server实例后，它只具有表product的所有权限。
*/ USE company --创建角色 rtest
EXEC sp_addrole 'rtest' --授予 rtest 对product 表的所有权限
GRANT ALL ON product TO rtest
--授予角色 rtest 对 employees 表的 SELECT 权限
GRANT SELECT ON titles TO rtest --添加登录 ltest,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'ltest','pwd','company' --为登录 ltest 在数据库 pubs 中添加安全账户 utest
EXEC sp_grantdbaccess 'ltest','utest' --添加 utest 为角色 rtest 的成员
EXEC sp_addrolemember 'rtest','utest' --拒绝安全账户 utest 对 employees 表的 SELECT 权限
DENY SELECT ON employees TO utest /*--完成上述步骤后,用 ltest 登录,可以对company表进行所有操作,但无法对employees表查询,虽然角色 rtest 有employees表的select权限,但已经在安全账户中明确拒绝了对employees的select权限,所以ltest无employees表的select权限--*/ --从数据库 company 中删除安全账户
EXEC sp_revokedbaccess 'utest' --删除登录 ltest
EXEC sp_droplogin 'ltest' --删除角色 rtest
EXEC sp_droprole 'rtest' |
● 总结：
在新建登陆时，利用服务器角色分配账户的功能，利用数据库角色管理用户对数据库进行的操作。在新建角色时为角色分配权限，来限制用户的操作。
懂得SQL Server安全性的机制后，我们就可以开发自己的数据库安全策略了。你下一步所需要的可能就是产生一个SQL Server脚本了。在SQL Server企业管理器中，右击一个数据库，选择“所有任务”，选择“生成SQL脚本”，这个选项能够产生一个脚本，对包括安全策略在内的数据库进行更新。一个脚本文件可以代替通过鼠标在SQL Server事件管理器中进行点击和选择的操作，大大减少DBA的工作量。