sqlserver登录名及角色权限的那些事

知识就是力量，时间就是生命。这篇文章主要讲述sqlserver登录名及角色权限的那些事相关的知识，希望能为你提供帮助。
对于初学者来说，sqlsever的登录名和用户名，总是非常难以理解和区分。sqlsever的角色权限控制，并不像oracle中控制的那么完善。往往sqlsever的登录名和角色权限，这一块的操作就成为了初学都的绊脚石。
登录名：默认情况下，使用登录名只能登录访问sqlsever数据库，但是不能让用户访问服务器中的数据库资源。
用户名：一个或多个登录对象在数据库中的映射，可以对用户对象进行授权，以便为登录对象提供对数据库的访问权限。
链接或登录Sql Server服务器时是用的登录名，而非用户名登录的。在程序里面的链接字符串中，使用的用户名也是指登录名。
下面给出实例说明，比如在java中的通过jdbc操作sqlsever数据库：
package com.bigdata.dafeige.jdbc;
import java.sql.*;
public class Main {
public static void main(String []args) { //主函数，程序运行的入口
//设置jdbc中使用到的变量
String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver"; //sqlsever的驱动类
String dbURL="jdbc:sqlserver://sqlsever服务器IP:1433; DatabaseName=数据库名称"; //定义数据库连接信息
String userName="sqlsever登录名"; //sqlserver登录名
String userPwd="sqlsever登录名的密码"; //sqlserver登录名设置的密码
try
{
Class.forName(driverName); //加载驱动类
Connection dbConn=DriverManager.getConnection(dbURL,userName,userPwd); //创建数据库连接
System.out.println("success!"); //如果走到这，证明没有出现异常，连接数据库就成功了，输出提示信息
}
catch(Exception e)
{
e.printStackTrace();
System.out.print("fail!"); //如果连接失败，被try catch捕获，输出提示信息
}
}
}
上面简单引入了一下今天的题，下面我们就开始操作起来吧！！！
一、修改sqlsever验证方式登录sa用户

文章图片

文章图片

【sqlserver登录名及角色权限的那些事】

文章图片

二、修改登录名sa信息

文章图片

1、修改密码

文章图片

2、修改角色

文章图片

3、用户映射sa登录后，默认可以操作哪些库，对于这些库有哪些权限

文章图片

4、状态

文章图片

三、登录帐号维护 1、创建登录帐号

文章图片

指定登录名和密码

文章图片

其中密码要符合安全策略：密码必须至少为8个字符，并且包含以下四种字符中的三种：大写、小写、10个基本数字(0-9)、符号
为此登录名指定角色

文章图片

设置操作的库和权限

文章图片

设置状态

文章图片

2、使用新建的帐号登录

文章图片

3、访问库中的表数据

文章图片

文章图片

四、创建数据库 1、创建数据库

文章图片

指定库名

文章图片

文章图片

提示权限不够，创建失败
2、登录sa授权

文章图片

文章图片

3、创建数据库重新登录sqlsever

文章图片

文章图片

五、创建表 1、创建表

文章图片

指定表结构(列及类型、宽度)

文章图片

关闭保存，定义表名

文章图片

文章图片

2、查看新建的表

文章图片

文章图片

六、使用命令行操作 1、创建登录名create login 登录名 with password=\'密码\',DEFAULT_DATABASE=默认库名
其中密码要符合安全策略：密码必须至少为8个字符，并且包含以下四种字符中的三种：大写、小写、10个基本数字(0-9)、符号

文章图片

刷新查看

文章图片

文章图片

2、登录名rename 2.1、图形界面操作

文章图片

2.2、命令操作
alter login 旧登录名 with name=新登录名

文章图片

刷新查看

文章图片

文章图片

3、删除登录名 3.1、图形界面操作

文章图片

3.2、命令操作
drop login 登录名

文章图片

刷新查看

文章图片

文章图片

七、服务器角色和数据库角色权限当几个用户需要在某个特定的数据库中执行类似的动作时(这里没有相应的Windows用户组)，就可以向该数据库中添加一个角色(role)。数据库角色指定了可以访问相同数据库对象的一组数据库用户。
A、添加或删除固定服务器角色成员sp_addsrvrolemember
sp_dropsrvrolemember
注意：不能添加、修改或删除固定服务器角色。另外，只有固定服务器角色的成员才能执行上述两个系统过程来从角色中添加或删除登录账户。
B、sa登录sa登录是系统管理员的登录。在以前的SQL Server版本中不存在角色，sa登录具有所有可能的关于系统管理工作的权限。在SQL Server 2005中，sa登录保持了向后兼容性。sa登录永远是固定服务器角色syadmin中的成员，并且不能从该角色中删除。
注意：只有当没有其他方法登录到SQL Server系统中时，再使用sa登录。
C、固定服务器角色及其权限在某个SQL Server系统中，每个固定服务器角色都有其隐含的权限。使用系统过程sp_srvrolepermission可以浏览每个固定服务器角色的权限。该系统过程的语法形式为：sp_srvrolepermission[[@srvrolename =] ‘role’]
如果没有指定role的值，那么所有的固定服务器角色的权限都将显示出来。下面的部分将讨论每个固定服务器角色的权限。

文章图片

1. sysadmin
固定服务器角色sysadmin的成员被赋予了SQL Server系统中所有可能的权限。例如，只有这个角色中的成员(或一个被这个角色中的成员赋予了CREATE DATABASE权限的用户)才能够创建数据库。
固定服务器角色和sa登录之间有着特殊的关系。sa登录一直都是固定服务器角色中的成员，并且不能从该角色中删除。
2. serveradmin
固定服务器角色serveradmin的成员可以执行如下的动作：
向该服务器角色中添加其他登录
运行dbcc pintable命令(从而使表常驻于主内存中)
运行系统过程sp_configure(以显示或更改系统选项)
运行reconfigure选项(以更新系统过程sp_configure所做的所有改动)
使用shutdown命令关掉数据库服务器
运行系统过程sp_tableoption为用户自定义表设置选项的值
3. setupadmin
固定服务器角色setupadmin中的成员可以执行如下的动作：
向该服务器角色中添加其他登录
添加、删除或配置链接的服务器
执行一些系统过程，如sp_serveroption
4. securityadmin
固定服务器角色securitypadmin中的成员可以执行关于服务器访问和安全的所有动作。这些成员可以进行如下的系统动作：
向该服务器角色中添加其他登录
读取SQL Server的错误日志
运行如下的系统过程：如sp_addlinkedsrvlogin、sp_addlogin、sp_defaultdb、sp_defaultlanguage、sp_denylogin、sp_droplinkedsrvlogin、sp_droplogin、sp_grantlogin、sp_helplogins、sp_remoteoption和sp_revokelogin(所有这些系统过程都与系统安全相关。)
5. processadmin
固定服务器角色processadmin中的成员用来管理SQL Server进程，如中止用户正在运行的查询。这些成员可以进行如下的动作：
向该服务器角色中添加其他登录
执行KILL命令(以取消用户进程)
6. dbcreator
固定服务器角色dbcreator中的成员用来管理与数据库创建和修改有关的所有动作。这些成员可以进行如下的动作：
向该服务器角色中添加其他登录
运行CREATE DATABASE和ALTER DATABASE语句
使用系统过程sp_renamedb来修改数据库的名称
7. diskadmin
固定服务器角色diskadmin的成员可以进行如下与用来存储数据库对象的文件和文件组有关的动作：
向该服务器角色中添加其他登录
运行如下系统过程：sp_ddumpdevice和sp_dropdevice。
运行DISK INIT语句
D、固定数据库角色固定数据库角色在数据库层上进行定义，因此它们存在于属于数据库服务器的每个数据库中。下表列出了所有的固定数据库角色

文章图片

除了上表中列出的固定数据库角色之外，还有一种特殊的固定数据库角色，名为public，这里将首先介绍这一角色。
public角色
public角色是一种特殊的固定数据库角色，数据库的每个合法用户都属于该角色。它为数据库中的用户提供了所有默认权限。这样就提供了一种机制，即给予那些没有适当权限的所有用户以一定的(通常是有限的)权限。public角色为数据库中的所有用户都保留了默认的权限，因此是不能被删除的。
一般情况下，public角色允许用户进行如下的操作：
使用某些系统过程查看并显示master数据库中的信息
执行一些不需要一些权限的语句(例如PRINT)
固定数据库角色及其权限
在数据库中，每个固定数据库角色都有其特定的权限。这就意味着对于某个数据库来说，固定数据库角色的成员的权限是有限的。使用系统过程sp_dbfixedrolepermission，就可以查看每个固定数据库角色的权限。该系统过程的语法为：sp_db.xedrolepermission [[@rolename =] ‘role’]
如果没有指定role的值，那么所有固定数据库角色的权限都可以显示出来。下面的几节将讨论每个固定数据库角色的权限。
1. db_owner
固定数据库角色db_owner的成员可以在特定的数据库中进行如下的动作：
向其他固定数据库角色中添加成员，或从其中删除成员
运行所有的DDL语句
运行BACKUP DATABASE和BACKUP LOG语句
使用CHECKPOINT语句显式地启动检查点进程
运行下列dbcc命令：dbcc checkalloc、dbcc checkcatalog、dbcc checkdb、dbcc updateusage
授予、取消或剥夺每一个数据库对象上的下列权限：SELECT、INSERT、UPDATE、DELETE和REFERENCES
使用下列系统过程向数据库中添加用户或角色：sp_addapprole、sp_addrole、sp_addrolemember、sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole、sp_droprolemember、sp_dropuser、sp_grantdbaccess
使用系统过程sp_rename为任何数据库对象重新命名
2. db_accessadmin
固定数据库角色db_accessadmin的成员可以执行与数据库访问有关的所有动作。这些角色可以在具体的数据库中执行下列操作：
运行下列系统过程：sp_addalias、sp_dropalias、sp_dropuser、sp_grantdbacess、sp_revokedbaccess
为Windows用户账户、Windows组和SQL Server登录添加或删除访问
3. dbdatareader
固定数据库角色dbdatareader的成员对数据库中的数据库对象(表或视图)具有SELECT权限。然而，这些成员不能把这个权限授予其他任何用户或角色。(这个限制对REVOKE语句来说同样成立。)
4. dbdatawriter
固定数据库角色dbdatawriter的成员对数据库中的数据库对象(表或视图)具有INSERT、UPDATE和DELETE权限。然而，这些成员不能把这个权限授予其他任何用户或角色。(这个限制对REVOKE语句来说也同样成立。)
5. db_ddladmin
固定数据库角色db_ddladmin的成员可以进行如下的动作：
运行所有DDL语句
对任何表上授予REFERENCESE权限
使用系统过程sp_procoption和sp_recompile来修改任何存储过程的结构
使用系统过程sp_rename为任何数据库对象重命名
使用系统过程sp_tableoption和sp_changeobjectowner分别修改表的选项和任何数据库对象的拥有者
6. db_securityadmin
固定数据库角色db_securityadmin的成员可以管理数据库中的安全。这些成员可以进行如下的动作：
运行与安全有关的所有Transact-SQL语句(GRANT、DENY和REVOKE)
运行以下系统过程：sp_addapprole、sp_addrole、sp_addrolemember、sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole、sp_droprolemember
7. db_backupoperator
固定数据库角色db_backupoperator的成员可以管理数据库备份的过程。这些成员可以进行如下动作：
运行BACKUP DATABASE和BACKUP LOG语句
用CHECKPOINT语句显式地启动检查点进程
运行如下dbcc命令：dbcc checkalloc、dbcc checkcatalog、dbcc checkdb、dbcc updateusage
8. db_denydatareader和db_denydatawriter
顾名思义，固定数据库角色db_denydatareader的成员对数据库中的数据库对象(表或视图)没有SELECT权限。如果数据库中含有敏感数据并且其他用户不能读取这些数据，那么就可以使用这个角色。固定数据库角色db_denydatawriter的成员对数据库中的任何数据库对象(表或视图)没有INSERT、UPDATE和DELETE权限。
E、自定义数据库角色在实际的数据库管理中，对于用户的权限控制的非常严格，某些用户就只能对表进行CRUD操作。数据库固定角色，不能完成这样的权限控制，我们就需要自定义数据库角色。
1、新建数据库角色
找到要操作的数据库