sqlsever数据库用户管理

目录

1. 概述

在SQL Server中，用户和角色都是分为服务器级别和数据库级别的。

2. 服务器级别

2.1 服务器级用户（即：登录名）

服务器级别下的登录名是指：有权限登录到某个SQL Server服务器的用户。

例如：超级管理员的登录名是sa；

登录名具体位置在： 数据库——>安全性——>登录名

2.2 服务器级角色

服务器级角色是指：为帮助您管理服务器上的权限，SQL Server 提供了若干角色，这些角色是用于对其他主体进行分组的安全主体。
服务器级角色的权限作用域为服务器范围。SQL Server 提供了九种固定服务器角色， 无法更改授予固定服务器角色的权限，这9组角色分别如下：

• sysadmin —— 在SQL Server中进行任何活动，该角色的权限跨越所有其它固定服务器角色；
• serveradmin —— 更改服务器范围内的配置选项并关闭服务器；
• setupadmin —— 添加和删除链接服务器，并执行某些系统存储过程（如 sp_serveroption）；
• securityadmin —— 授予数据库引擎的访问权限和配置用户权限的能力使得安全管理员可以分配大多数服务器权限，securityadmin 角色应视为与sysadmin 角色等效；
• processadmin —— 管理在 SQL Server 实例中运行的进程；
• dbcreator —— 创建和改变数据库，但是没有读取权限；
• diskadmin —— 管理磁盘文件；
• bulkadmin —— 执行 BULK INSERT 语句；
• public —— 每个 SQL Server 登录名均属于 public 服务器角色。 如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限，该用户将继承授予该对象的 public 角色的权限。当您希望该对象对所有用户可用时，只需对任何对象分配 public 权限即可。您无法更改 public 中的成员关系。public 的实现方式与其他角色不同，但是，可以从 public 授予、拒绝或撤销权限。

服务器角色具体位置在： 数据库——>安全性——>服务器角色

注意：

登录名一定属于某些角色，默认为public；

服务器角色不允许更改；

登录后也不一定有权限操作数据库；

3. 数据库级别

3.1 数据库级用户

数据库级用户是指：有权限能操作（比如：增删查改等权限）数据库的用户。

用户具体位置在 数据库——>某个具体库——>安全性——>用户

3.2 数据库级角色

SQL Server 中有两种类型的数据库级角色：

• 数据库中预定义的 “固定数据库角色” ；
• 您可以创建的 “灵活数据库角色” ；

固定的有某些权限的数据库角色，所有数据库中都有这些角色，默认有10个，分别如下：

1. public

public 角色是一个特殊的数据库角色，每个数据库用户都属于它。public 角色：

捕获数据库中用户的所有默认权限。
无法将用户、组或角色指派给它，因为默认情况下它们即属于该角色。
包含在每个数据库中，包括 master、msdb、tempdb、model 和所有用户数据库。
无法除去。

2. db_owner

进行所有数据库角色的活动，以及数据库中的其它维护和配置活动。
该角色的权限跨越所有其它固定数据库角色。

3. db_accessadmin

在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和用户以及 SQL Server 用户。

4. db_datareader

查看来自数据库中所有用户表的全部数据。

5. db_datawriter

添加、更改或删除来自数据库中所有用户表的数据

6. db_ddladmin

添加、修改或除去数据库中的对象(运行所有 DDL)

7. db_securityadmin

管理 SQL Server 2000 数据库角色的角色和成员，并管理数据库中的语句和对象权限

8. db_backupoperator

有备份数据库的权限

9. db_denydatareader

拒绝选择数据库数据的权限

10. db_denydatawriter

拒绝更改数据库数据的权限

数据库角色具体位置在： 数据库——>某个具体库——>安全性——>角色——>数据库角色

注意：

请不要将灵活数据库角色添加为固定角色的成员，这会导致意外的权限升级。

3.3 架构

架构（Schema）是一组数据库对象的集合，它被单个负责人（可以是用户或角色）所拥有并构成唯一命名空间。可以将架构看成是对象的容器。

数据库用户对应于服务器登录名以便登录者可以操作数据库
数据库角色可以添加，可以定制不同权限　　
数据库架构，类似于数据库对象的命名空间，用户通过架构访问数据库对象

架构数据库角色具体位置在： 数据库——>某个具体库——>安全性——>架构

3.4 数据库级别设置

服务器级 -> 数据库级 -> 架构级 - > 数据对象级

比如：Server.DataBase1.dbo.Table1，这里的意思就是Table1这个表属于dbo这个架构，dbo这个架构属于DataBase1这个数据库，DataBase1这个数据库属于Server这个服务器。里面的架构其实就是一个容器，好像就是面向对象里面的命名空间，一个用户可以拥有多个架构，但是不能对没有拥有的架构进行操作。一个数据库角色，是对不同架构里面数据对象的权限组织，也有可能涉及到多个架构，当某一个用户被转换成一种数据库角色的时候，假如这个用户本身不拥有某一个架构而该数据库角色拥有，那它当它对那个架构进行操作的时候就会出错。

3.5 相互之间的关系

1. SQL Server服务器登录名与数据库用户在服务器级别是一对多的，在数据库级别是一对一的。

登录名可以理解为进入整个大楼的钥匙，用户名可以理解为一个房间的钥匙，这里所说的大楼就是sql server服务器，而房间就是这个sql server服务器中的具体的库，要注意登录名是存在于master数据库的syslogins表中，用户名是在各个具体的库下建立的(也就是要先进入各个库)， 最关键的是：一个登录名可以和多个不同库下的用户做映射，但是在同一个库下只能和一个用户做映射，并且一个用户名可以和多个登录名有映射关系， 真正有权限的是用户名，只不过是用登录名去登进数据库，然后去找映射的用户名，这样就有了相应的权限，刚开始建立的登录名只要把该登录名的服务器角色设置为Database Creator，就可以创建新的数据库了，并且新建立的这个数据库中会自动生成两个用户名：dbo和guest。并且刚刚建立的登录名就和dbo映射了，还有就是rbo也和sa映射了，因为sa作为管理员的登录名和每个库中的rbo用户映射。

3. 一个数据库角色有可能涉及多个架构，数据库角色对应的是权限。
4. 一个用户对应一个数据库角色。
5. 一个数据库用户可以对应多个架构（架构是表容器），架构里面包含的是数据库表。

4. 使用sql语句为数据库用户赋予权限

4.1 回顾创建数据库

use master; 
go  

create database E_Market 
on primary
(
	name="E_Market_data", 
	filename="/var/opt/mssql/data/E_Market_data.mdf",
	size=5MB, 
	maxsize=100MB, 
	filegrowth=15%

)

log  on
(
	name="E_Market_log", 
	filename="/var/opt/mssql/data/E_Market_log.ldf",
	size=5MB, 
	filegrowth=0

)

go 

4.2 创建登录sqlsever服务器的登录名

create login '登录名' with password='密码';
go

4.3 创建数据库用户

create user '数据库用户名' for login '登录名' ;
go

4.4 为数据库用户赋予数据库操作权限

grant '操作权限' on '数据库.表名' to '数据库用户名';
go