SqlServer2008之关于”***对象无效“错误

我们在sql server2008中编写sql语言时，经常会遇到这样的提示：***对象无效。这是什么错误，为什么在2000版本中就不存在这样的情况呢？其实这主要是sql server 2005/2008版本中新增了架构的概念。下面就结合网上的查询说一下我对sql server2008中架构的理解。可能有一些东西理解的不到位，欢迎各位指出，共同学习、改进。

1、定义

首先，我们来看一下微软对架构的官方定义:架构(Schema)是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。个人感觉这个定义有点晦涩，我理解的架构就是数据库中用来存放表（数据库对象）的命名空间。数据库这个大的命名空间下可以包括多个架构（小的命名空间），每个架构下又可以包含多张表。

架构实际上早在SQL SERVER 2000中就已经存在，不过在SQL SERVER 2000中数据库用户和架构是隐式连接在一起的,，每个数据库用户都是与该用户同名的架构的所有者。当我们使用查询分析器去查询一个表的时候，一个完整的表的名称应该包括服务器名.数据库名.用户名.对象名，而在SQL SERVER 2005/2008中一个表的完全限定名称应该为服务器名.数据库名.架构名.对象名。在SQL SERVER 2000中的完全限定名称中的“用户名”也是数据库中的用户，也是“架构名”。假如有一个账户df在test数据库中创建了一张表tb1的时候，在查询分析器中应该输入的查询语句为select * from test.df.tb1，也就是说，在SQL SERVER 2000中一张表所属的架构默认就是表的创建者的登录名称，用户可以修改他所创建的所有数据库对象。但在2008中已经将用户和其创建对象所属关联取消了，而加入了一个全新的架构体系。

2、引入

那微软为何要在05之后的版本中引入架构，并将用户与架构分开呢？将架构与数据库用户分离对管理员和开发人员而言有什么好处呢？

1.架构管理与用户管理分开。多个用户可以通过角色（role）或组（Windows groups）成员关系拥有同一个架构。在SQL SERVER 2005/2008 中，每个数据库角色都有一个属于自己的架构，如果我们创建一个表，给它指定的架构名称为 db_ddladmin，那么任何一个属于db_ddladmin中的用户都是可以去查询、修改和删除属于这个架构中的表，但是不属于这个组的用户是没有对这个架构中的表进行操作的权限，有一点必须注意，db_dbdatareader组的成员可以查看所有数据库中的表，db_dbdatawriter组成员可以修改所有数据库中 的表，db_owner组成员可以对数据库所有表进行所有操作，这几个组的成员可以通过角色获取到在数据库中的特殊权限。

2.在创建数据库用户时，可以指定该用户账号所属的默认架构。 （ 建议大家指定）

3. 删除数据库用户变得极为简单。在 SQL Server 2000 中，用户（User）和架构是隐含关联的，即每个用户拥有与其同名的架构。因此要删除一个用户，必须先删除或修改这个用户所拥有的所有数据库对象，就比如 一个员工要离职要删除他的账户的时候，还得将他所创建的表和视图等都删除，影响过大。SQL SERVER 2005/2008将架构和对象者分离后就不在存在这样的问题，删除用户的时候不需要重命名该用户架构所包含的对象，在删除创建架构所含对象的用户后，不再需要修改和测试显式引用这些对象的应用程序。

4. 共享缺省架构使得开发人员可以为特定的应用程序创建特定的架构来存放对象，这比仅使用管理员架构（DBO schema）要好。

5. 在架构和架构所包含的对象上设置权限（permissions）比以前的版本拥有更高的可管理性。

6. 区分不同业务处理需要的对象，例如，我们可以把公共的表设置成pub的架构，把销售相关的设置为sales，这样管理和访问起来更容易。大多数用户在创建对象的时候习惯直接输入对象名而将对象的架构名称省略，在2005/2008 中，会给用户创建的这样的表加上一个缺省的架构，用户如果没有对自己的默 认架构做设置，那缺省架构就是dbo，也就是说，如果一个db_ddladmin的成员在数据库中创建一个没有加上架构名称的表，这个表在数据库中的完整 名称应该是dbo.表名，创建者在数据库中如果不是属于其它特殊组的成员，是不能对特殊组成员创建的表进行任何修改和查询的。

7.若不指定默认架构，则为DBO，为了向前兼容，早期版本中的对象迁移到新版本中，早期版本中没有架构的概念的。所以就该对象的架构名就是dbo.在SQL Server 2008中，DBO就是一个架构

8.当查找对象时，先找与用户默认架构相同的架构下的对象，找不到再找DBO的对象

3、架构的作用

用户与架构分开，可以让数据库内各对象不再绑在某个用户账号上，可以解决SQL SERVER 2000及以前版本中”用户离开公司“问题，也就是在拥有该对象的用户离开公司，或离开该职务时，不必要大费周章地更改该用户所有的对象属于新的用户所有。另外，也可在安装某个套装软件时，设置该套装软件所用的数据库对象都属于某个特定的架构，容易区别。也就是说，在单一数据库内，不同部门或目的的对象，可以通过架构区分不同的对象命名原则与权限。

在SQLServer2005/2008中，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权。并且可以在架构中创建具有用户友好名称的对象，明确指示对象的功能。例如，除了cus.app.entry.customEntry外，您还可以创建名为cus.app.manifest.customEntry的架构。因为“manifest”不是用户，所以从数据库中删除用户后，无需更改此名称。这就简化了数据库管理员和开发人员的工作。

SQL Server 2005/2008 还引入了“默认架构”的概念，用于解析未使用其完全限定名称引用的对象的名称。在 SQL Server 2000 中，首先检查的是调用数据库用户所拥有的架构，然后是 DBO 拥有的架构。在 SQL Server 2005 /2008中，每个用户都有一个默认架构，用于指定服务器在解析对象的名称时将要搜索的第一个架构。可以使用 CREATE USER 和 ALTER USER 的 DEFAULT_SCHEMA 选项设置和更改默认架构。如果未定义 DEFAULT_SCHEMA，则数据库用户将把 DBO 作为其默认架构。 下面举个具体的示例来说明以一下架构的作用。

4、实例

--命令对架构进行操作
use master

go
setuser
go
--创建测试数据库
create database schTest
go 
create login df with password='sj1234',default_database=schTest
create login xhl with password='sj1245',default_database=schTest

go
use schTest
go
-- 创建两个用户时没有指定属于哪个架构
create user df for login df 
create user xhl for login xhl

-- 这个表没指定属于哪个架构属于默认DBO 架构
go
create table tb1 (姓名 varchar(8),性别 char(2))

--这个表就属于sch架构
go
create schema sch
go
create table sch.tb2(姓名 varchar(8),性别 char(2),年龄 int)

-- 赋予df这个用户查询sch架构中的对象的权限. 
go
grant select on schema::sch to df 

go
setuser 'df'  --切换用户df
select * from  tb2
-- 此时报告"对象名无效" 因为tb2此时为默认的DBO架构，而实际上tb2属于sch架构.
--带上架构名称，就可以查询了
go
select * from sch.tb2


go
setuser  -- 切换到sa

---切换到xhl
setuser 'xhl'
--不能查询,是因为没有权限
select * from sch.tb2 

go
setuser --切换sa
--给df用户赋默认架构
alter user df with default_schema=sch

go
setuser 'df'  --切换df
-- 此时不需要指定sch 也可以了,如果架构中还有其他对象，也可以查询
select * from tb2

go
setuser  --切换sa
--创建第三张测试表，同样的是sch架构下
create table sch.tb3 (id int,uname varchar(8))

go
--切换用户df
setuser 'df'
---可以进行查询
select * from tb3

go
---但是无法进行数据插入，因为没有插入权限
insert into tb3 values (1,'abcde')   --拒绝了insert权限

go
setuser
--赋插入权限
grant  insert on schema::sch to df

--切换用户df
setuser 'df'
go
---可以进行数据插入
insert into tb3 values (1,'abcde')   --OK! 
---查询结果
select * from tb3

GO
Grant alter on schema::sch to df --  使df 这个用户对架构中的数据对象有可更改的能力。

--错误
--无法对sa、dbo、实体所有者、information_schema、sys 或您自己授予、拒绝或撤消权限。
GO
Use master
go
Grant control server to df  -- 使df这个用户能够控制服务器。


--错误
--无法对sa、dbo、实体所有者、information_schema、sys 或您自己授予、拒绝或撤消权限。
setuser
go
use schtest
go
--创建架构sch1
create schema sch1

 
go
-- 修改对象的架构tb2表的架构由sch 转移到sch1
alter schema sch1 transfer sch.tb2

go
--创建一个新用户，同时指定默认schema,默认属于DBO 
create login yhy with password='sj1234',default_database=master
GO
use schTest
GO
create user yhy for login yhy with default_schema=sch --属于sch


--切换用户yhy
setuser 'yhy'
--查询表,对象名'tb2' 无效。
select * from tb2

go
setuser
--赋架构权限
grant select on schema::sch1 to yhy
--切换用户yhy

setuser 'yhy'
go
select * from tb2   ---还是无效，因为不在同一个schema

go
setuser
go
alter user yhy with default_schema=sch1   --更改yhy的默认架构

GO
--由于yhy不是当前用户，所以无法进行查询
select * from tb2  
--显示当前用户
GO
select user
GO
--切换用户yhy
setuser 'yhy'
-- 此时就可以进行查询了,如果架构中还有其他对象，也可以查询
go
select * from tb2    
--显示当前用户
select user

go
setuser
go
use master
go
---删除用户
drop user df
drop user xhl
drop user yhy

---删除登录名
drop login df
drop login yhy
drop login xhl

--删除数据库
drop database schTest