SQL 用户，架构， 角色，权限

1、如果在删除用户的时候出现无法删除用户，此用户拥有架构。那么是因为当前这个用户隶属于某个架构，我们在创建用户的时候默认隶属于dbo架构的，如果你指定了非dbo架构那么就必须解除架构的绑定才能删除这个用户，隶属于dbo架构除外。

2、我们在软件或者系统开发的过程中，可能有很多的人对数据库进行操作，难免会出现某个人对数据的误操作，那么怎么避免这种情况呢？

通过创建指定的用户，然后给于用户指定角色并对角色赋予相应的权限即可避免其他的误操作。

a、创建角色

Create Role TestRole

b、给予角色赋予相应的权限

Grant create table,create procedure,create view to TestRole--创建表 视图 存储过程的权限

Grant Select,insert,execute,alter to TestRole--查询，添加，执行存储过程，修改存储过程、视图、表

c、创建登陆

Create Login TestLogin with password='123456'

d、创建用户

Create User TestUser for Login TestLogin --一般我们设置登录名和用户名一致，这里为了区分，不一致

e、添加用户到角色中

exec sp_addrolemember 'TestRole','TestUser'

至此用户创建结束，我们打开SSMS，在登录名输入TestUser，密码为123456即可进入到管理界面

在查询分析器执行Update Table set ceshi='1' 会出现什么错误呢？

消息 229，级别 14，状态 5，第 1 行
拒绝了对对象 'Table' (数据库 'Study'，架构 'dbo')的 UPDATE 权限。

因为我们没有给予这个用户update的权限，只给了select 、insert 、execute 、alter

当然权限还有很多，比如赋予角色的执行评估计划的权限

Grant showplan to TestRole

3、关于架构和用户

我记得有个网友对架构和用户的关系有个很好的比喻：数据库中有表、存储过程、视图、触发器等很多的对象，就像一个网站中有网页、脚本、文件夹等对象一样，暂称之为网站对象。我们都知道网站中的对象我们都是很有条理的放在不同的文件夹下面，所以数据库对象也不能都放在一堆。那么相对于网站的对象数据库对象的架构是什么意思呢？很明显就是文件夹了。

那是不是架构和文件夹完全相同呢？答案是：不是。文件夹可以多次嵌套，架构却不可以。一个对象只能属于一个架构，就像一个文件只能存放于一个文件夹。

我们访问一个数据库对象应该是："架构名。对象名"

列出数据所有的架构

select sys.objects.name,sys.schemas.name
     from   sys.objects,sys.schemas
    where sys.objects.type='U' and   sys.objects.schema_id=sys.schemas.schema_id

架构-用户：一对多的关系

架构-角色：多对多的关系

     a.一个架构中不能包含相同名称的对象，相同名称的对象可以在不同的架构中存在。

 

 

     b.一个架构只能有一个所有者，所有者可以是用户, 数据库角色, 应用程序角色。

     c.一个用数据库角色可以可以拥有一个默认架构，和多个架构。

     d.多个数据库用户可以共享单个默认架构。

     e.由于架构与用户独立，删除用户不会删除架构中的对象。

4.架构所有者和权限

任何数据库主体都可以拥有架构，并且一个主体可拥有多个架构。您可以对架构应用安全规则，安全规则将由架构中的所有对象继承。如果设置了对架构的访问权限，则当新对象添加到架构时，新对象会自动应用这些权限。可以为用户分配一个默认的架构，且多个数据库用户可以共享同一架构。

默认情况下，当开发人员在架构中创建对象时，该对象由拥有架构的安全主体而不是开发人员拥有。可以使用 ALTER AUTHORIZATION Transact-SQL 语句转移对象所有权。虽因架构会增大管理权限的复杂度而不建议使用，但架构仍然可以包含由不同用户拥有的对象并可具有比分配给架构的权限更加细化的权限。对象可以在架构之间移动，架构所有权也可以在主体之间转移。可以在不影响架构的情况下删除数据库用户。

5. 内置架构

SQL Server 随附了十个预定义的架构，它们与内置数据库用户和角色具有相同的名称。这些架构主要用于向后兼容性。如果您不需要与固定数据库角色具有相同名称的架构，则可以删除它们。您不能删除下列架构：

• dbo

• guest

• sys

• INFORMATION_SCHEMA

如果从模型数据库中删除这些架构，它们将不会显示在新数据库中。sys 和 INFORMATION_SCHEMA 架构是为系统对象而保留的。您不能在这些架构中创建对象，而且不能删除它们。

6.dbo 架构

dbo 是新创建的数据库的默认架构。 dbo 架构由 dbo 用户帐户拥有。默认情况下，使用 CREATE USER Transact-SQL 命令创建的用户的默认架构为 dbo。

分配了 dbo 架构的用户不继承 dbo 用户帐户的权限。用户不从架构继承权限；架构权限由架构中包含的数据库对象继承。

当使用部分名称来引用数据库对象时，SQL Server 首先在用户的默认架构中查找。如果在此处未找到该对象，则 SQL Server 其次将在 dbo 架构中查找。如果对象不在 dbo 架构中，则会返回一个错误。

7.  架构体系

使用图形表示，这样我们更能方便的了解架构，和她在安全对象中所处的位置及与其他安全对象的关系。

找到一张，如图，数据库引擎 权限层次结构之间的关系图：



，这能帮助我们理解架构。

1.一个架构中不能包含相同名称的对象，相同名称的对象可以在不同的架构中存在。

2.一个架构只能有一个所有者，所有者可以是用户, 数据库角色, 应用程序角色。

3.一个用数据库角色可以可以拥有一个默认架构，和多个架构。

4.多个数据库用户可以共享单个默认架构。

5.由于架构与用户独立，删除用户不会删除架构中的对象。