alt

角色和用户

在数仓的运维工作中，经常需要为用户开通不同权限的账号，使用户可以正常访问不同的数据，那么这就需要我们了解SQL Server的权限体系。

名词解释

登录名： 用来登录服务器的用户账号，例：sa，这个就是默认的可以登录SQL Server服务器的超级管理员账号。

用户名： 用来登录数据库的访问账号，例：有些时候，我需要赋予某些用户权限，只可以对某个数据库有操作，不能对服务器有操作，这种情况下就需要使用用户名。

登录名和用户名，在服务器层级的关系是一对多，在数据库层级的关系是一对一。

角色： 角色分为两类，一类是服务器角色，一类是数据库角色。

服务器角色：指的是登录名登录到服务器后，对服务器所具有的权限。

数据库角色：指的是用户名访问到数据库后，对数据库所具有的权限。

系统中默认的角色分类如下：

类别 角色 描述
服务器 sysadmin       以在SQLServer中执行任何活动。 
服务器 serveradmin    以设置服务器范围的配置选项，关闭服务器。 
服务器 setupadmin     以管理链接服务器和启动过程。 
服务器 securityadmin   以管理登录和CREATE、DATABASE权限，还以读取错误日志和更改密码。 
服务器 processadmin  以管理在SQLServer中运行的进程。 
服务器 dbcreator       以创建、更改和除去数据库。 
服务器 diskadmin      以管理磁盘文件。 
服务器 bulkadmin      以执行BULKINSERT语句。
数据库 db_owner              数据库中有全部权限。 
数据库 db_accessadmin   以添加或删除用户ID。 
数据库 db_securityadmin   以管理全部权限、对象所有权、角色和角色成员资格。 
数据库 db_ddladmin        以发出ALLDDL，但不能发出GRANT、REVOKE或DENY语句。 
数据库 db_backupoperator  以发出DBCC、CHECKPOINT和BACKUP语句。 
数据库 db_datareader      以选择数据库内任何用户表中的所有数据。 
数据库 db_datawriter       以更改数据库内任何用户表中的所有数据。 
数据库 db_denydatareader   不能选择数据库内任何用户表中的任何数据。
数据库 db_denydatawriter  不能更改数据库内任何用户表中的任何数据。

数据库架构：

数据库架构（Schema）在不同的数据库中定义是不同的，这里只阐述SQL Server中架构的定义。

Schema官方的解释叫做数据库逻辑对象的集合，说的通俗一点就是将数据库中的对象进行分组，以方便管理和进行权限管控。

举个例子： 某个地方有一片工业园区，这个园区就是服务器； 在园区内，有很多企业落户在这里，这些企业就是数据库； 工业园区每天早上6点定时，大家可以进来工作，这个定时就是JOB； 白茶是整个工业园区的负责人，负责整个园区的所有事务，这时候白茶的角色是服务器角色； 除了负责园区以外，白茶也在这片园区里面开办了一家企业，或在里面的某家企业任职，这时候白茶的角色是数据库角色； 在园区，白茶是登录名，在企业内，白茶是用户名，且白茶可以在多家企业任职，所以在服务器层面，登录名和用户名的关系是一对多，落到具体的数据库层面，登录名和用户名的关系是一对一。 每个企业内，又分为不同的行政部门，这些部门就是数据库架构，将有关联关系的人分配到一起进行工作。 办公室里面装有监控，记录着大家日常的工作行为，监控就是日志。 某部门员工小A，在拿到合同以后，需要找主管审批，然后上报公司存档，这一过程就是存储过程；

白茶描述的可能有些地方不是特别的准确，只是为了让大家可以有个类似的概念。

使用实例

案例数据：

alt

在白茶本机的数据库中，存在名为“CaseData”的数据库。

例子1：

创建名为“Test”的登录名，使其具有服务器登录权限，不赋予数据库权限。

alt

alt

结果如下：

alt

例子2：

更新“Test”登录名的权限，赋予其“CaseData”数据库的只读权限。

alt

结果如下：

alt

alt

例子3：

更新“Test”登录名的权限，使其仅能查询“CaseData”数据库中的某张表。

alt alt

结果如下：

alt

例子4：

创建“Test”的Schema，并创建一张表，不对Test登录名开放。

CREATE SCHEMA Test

结果如下：

alt

CREATE TABLE [Test].[BAICHATEST](
  [Result] [int] NULL
)

结果如下：

alt

alt

alt

alt

这里是白茶，一个PowerBI的初学者。 alt