mysql db_owner_SQL Server修改数据库对象所有者(Owner)浅析

在SQL Server数据库中如何修改数据库对象(表、视图、存储过程..)的所有者(Owner)呢？一般我们可以使用系统提供的系统存储过程sp_changeobjectowner来修改。 我们先看看sp_changeobjectowner在MSDN的文档介绍吧

更改当前数据库中对象的所有者。重要提示：此存储过程只针对 Microsoft SQL Server 2000 中可用的对象进行。后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。另请使用 ALTER SCHEMA 或 ALTER AUTHORIZATION。sp_changeobjectowner 同时更改架构和所有者。若要保持与早期版本 SQL Server 的兼容性，如果当前所有者和新所有者拥有的架构名称与它们的数据库用户名相同，则此存储过程将只更改对象所有者。Transact-SQL 语法约定语法sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'参数[ @objname = ] 'object'当前数据库中现有表、视图、用户定义函数或存储过程的名称。object 是 nvarchar(776)，没有默认值。如果架构及其所有者具有相同的名称，则 object 可由现有对象所有者限定，格式为 existing_owner.object。[ @newowner=] 'owner '将成为对象的新所有者的安全帐户的名称。owner 的数据类型为 sysname，没有默认值。owner 必须是可访问当前数据库的有效数据库用户、服务器角色、Microsoft Windows 登录名或 Windows 组。如果新所有者是没有对应数据库级主体的 Windows 用户或 Windows 组，则将创建数据库用户。返回代码值0(成功)或 1(失败)注释sp_changeobjectowner 删除对象中的所有现有权限。在运行 sp_changeobjectowner 之后，必须重新应用要保留的任何权限。因此，建议首先编写现有权限的脚本，然后再运行sp_changeobjectowner。更改了对象的所有权之后，便可使用该脚本重新应用权限。在运行该脚本之前必须在权限脚本中修改对象所有者。有关数据库脚本的详细信息，请参阅编写数据库文档和脚本。若要更改安全对象的所有者，请使用 ALTER AUTHORIZATION.若要更改架构，请使用 ALTER SCHEMA。权限要求具有 db_owner 固定数据库角色的成员身份，或 db_ddladmin 固定数据库角色和 db_securityadmin 固定数据库角色的成员身份，同时还需要对对象具有 CONTROL 权限。

如上MSDN文档所描述的，系统存储过程的使用非常简单，如下所示

use test;goexec sp_changeobjectowner '[db_owner].[T1]','dbo';

批量修改数据库对象的所有者(owner)

执行上面存储过程过后，表对象T1的所有者(owner)就从db_owner改为了dbo了。如果一个数据库里面的表对象非常多，那么使用该方法就非常的繁琐了。此时就可以使用sp_MSforeachtable来批量处理该工作。

use test;goexec sp_MSforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '

但是使用sp_MSforeachtable结合系统存储过程sp_changeobjectowner，只能修改数据库里面所有表对象的所有者(owner)。并不能修改视图、存储过程、用户函数的所有者。那么应该如何批量修改存储过程、视图、用户自定义函数的所有者呢？ 其实也很简单，自己写个脚本将所有SQL Script脚本生成就OK了

SELECT  'exec sp_changeobjectowner '''  + USER_NAME(uid) +'.' + name + ''', ''dbo'';'from sys.sysobjects where xtype in ('V','P','F')

网上有个脚本对数据库所有对象所有者进行批量修改，已经相当全面了，在此就不重复造轮子了。

declare tb cursor local forselect 'sp_changeobjectowner ''['+replace(user_name(uid),']',']]')+'].['+replace(name,']',']]')+']'',''dbo'''from sysobjectswhere xtype in('U','V','P','TR','FN','IF','TF') and status>=0open tbdeclare @s nvarchar(4000)fetch tb into @swhile @@fetch_status=0beginexec(@s)fetch tb into @sendclose tbdeallocate tbgo

使用sp_changeobjectowner需要注意的地方

在使用系统函数sp_changeobjectowner时，你都会收到一条提示信息“注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。”,这个是因为系统函数sp_changeobjectowner虽然会修改数据库对象的所有者，但是，在视图、存储过程、用户自定义函数里面，如果你使用了owner.object_name这种写法，系统函数并不能检测到。所以当数据库对象修改过后，就有可能导致部分视图、存储过程出现错误，不太明白上面描述的，可以通过下面的例子理解一下。

USE Test;GOCREATE TABLE [db_owner].T1(ID   INT ,NAME VARCHAR(20));CREATE VIEW [db_owner].V_T1ASSELECT * FROM T1;CREATE VIEW [db_owner].V_T2ASSELECT * FROM db_owner.T1;CREATE PROCEDURE PRC_TEST_ONEASSELECT * FROM T1;GOCREATE PROCEDURE PRC_TEST_TWOASSELECT * FROM db_owner.T1;GO

修改了表T1的所有者后，视图[db_owner].V_T2、存储过程PRC_TEST_TWO都会报错。 如下截图所示。这也就是提示信息“注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。”所描述的情况。

exec sp_changeobjectowner 'db_owner.T1', 'dbo';

如果存在对应表的同义词，那么使用系统存储过程sp_changeobjectowner修改对象的所有者是会报错的。

CREATE TABLE [db_owner].T1(ID   INT ,NAME VARCHAR(20))CREATE SYNONYM T1FOR [db_owner].T1GOexec sp_changeobjectowner 'db_owner.T1', 'dbo';

sp_changeobjectowner这个系统存储过程的定义如下所示：

CREATE PROCEDURE Sp_changeobjectowner @objname  NVARCHAR(517),-- may be "[owner].[object]"@newowner SYSNAME-- must be entry from sysusersASSET nocount ONSET ansi_padding ONDECLARE @objid  INT,@newuid SMALLINT-- CHECK PERMISSIONS: Because changing owner changes both schema and--        permissions, the caller must be one of:-- (1) db_owner-- (2) db_ddladmin AND db_securityadminIF ( Is_member('db_owner') = 0 )AND ( Is_member('db_securityadmin') = 0OR Is_member('db_ddladmin') = 0 )BEGINRAISERROR(15247,-1,-1)RETURN ( 1 )END-- RESOLVE OBJECT NAME (CANNOT BE A CHILD OBJECT: TRIGGER/CONSTRAINT) --SELECT @objid = Object_id(@objname, 'local')IF ( @objid IS NULL )OR (SELECT parent_objFROM   sysobjectsWHERE  id = @objid) <> 0OR Objectproperty(@objid, 'IsMSShipped') = 1OR Objectproperty(@objid, 'IsSystemTable') = 1OR Objectproperty(@objid, 'ownerid') IN ( 0, 3, 4 )OR --public, INFORMATION_SCHEMA, system_function_schema-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:EXISTS (SELECT *FROM   sysdepends dWHERE  d.depid = @objid -- A dependency on this objectAND d.deptype > 0 -- that is enforcedAND @objid <> d.id-- that isn't a self-reference (self-references don't use object name)AND @objid <>-- And isn't a reference from a child object (also don't use object name)(SELECT o.parent_objFROM   sysobjects oWHERE  o.id = d.id))BEGIN-- OBJECT NOT FOUNDRAISERROR(15001,-1,-1,@objname)RETURN 1END-- RESOLVE NEW OWNER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) ----  Disallow aliases, and public cannot own objects --SELECT @newuid = uidFROM   sysusersWHERE  NAME = @newownerAND isaliased = 0AND uid NOT IN ( 0, 3, 4 )--public, INFORMATION_SCHEMA, system_function_schemaIF @newuid IS NULLBEGINEXECUTE Sp_msadduser_implicit_ntlogin@newownerSELECT @newuid = uidFROM   sysusersWHERE  NAME = @newownerAND isaliased = 0AND NAME <> 'public'ENDIF @newuid IS NULLBEGINRAISERROR(15410,-1,-1,@newowner)RETURN ( 1 )END-- CHECK IF CHANGING OWNER OF OBJECT OR ITS CHILDREN WOULD PRODUCE A DUPLICATEIF EXISTS (SELECT *FROM   sysobjectsWHERE  uid = @newuidAND NAME IN (SELECT NAMEFROM   sysobjectsWHERE  id = @objidOR parent_obj = @objid))BEGINRAISERROR(15505,-1,-1,@objname,@newowner)RETURN ( 1 )END-- DO THE OWNER TRANSFER (WITH A WARNING) --RAISERROR(15477,-1,-1)BEGIN TRANSACTION-- Locks Object and increments schema_ver.DBCC lockobjectschema(@objname)-- drop permissions (they'll be incorrect with new owner) --DELETE syspermissionsWHERE  id = @objidUPDATE sysobjectsSET    uid = @newuidWHERE  id = @objidUPDATE sysobjectsSET    uid = @newuidWHERE  parent_obj = @objidCOMMIT TRANSACTIONRETURN 0 -- sp_changeobjectownergo

其他方式修改数据库对象的所有者

使用ALTER SCHEMA修改数据库对象的所有者。如下所示：

ALTER SCHEMA dbo TRANSFER db_owner.T1;

GO