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

重要提示：此存储过程只针对 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 权限。

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

exec 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)

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

select sp_changeobjectowner [+replace(user_name(uid),],]])+].[

+replace(name,],]])+],dbo

from sysobjects

where xtype in(U,V,P,TR,FN,IF,TF) and status =0

open tb

declare @s nvarchar(4000)

fetch tb into @s

while @@fetch_status=0

begin

exec(@s)

fetch tb into @s

end

close tb

deallocate tb

go

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

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

exec 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 sysusers 

AS 

    SET nocount ON 

    SET ansi_padding ON 

    DECLARE @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_securityadmin 

    IF ( Is_member(db_owner) = 0 ) 

       AND ( Is_member(db_securityadmin) = 0 

              OR Is_member(db_ddladmin) = 0 ) 

      BEGIN 

          RAISERROR(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_obj 

            FROM   sysobjects 

            WHERE  id = @objid) 0 

        OR Objectproperty(@objid, IsMSShipped) = 1 

        OR Objectproperty(@objid, IsSystemTable) = 1 

        OR 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 d 

               WHERE  d.depid = @objid -- A dependency on this object 

                      AND d.deptype 0 -- that is enforced 

                      AND @objid d.id 

                      -- that isnt a self-reference (self-references dont use object name) 

                      AND @objid 

                          -- And isnt a reference from a child object (also dont use object name) 

                          (SELECT o.parent_obj 

                           FROM   sysobjects o 

                           WHERE  o.id = d.id)) 

      BEGIN 

          -- OBJECT NOT FOUND 

          RAISERROR(15001,-1,-1,@objname) 

          RETURN 1 

      END 

    -- RESOLVE NEW OWNER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) -- 

    --  Disallow aliases, and public cannot own objects -- 

    SELECT @newuid = uid 

    FROM   sysusers 

    WHERE  NAME = @newowner 

           AND isaliased = 0 

           AND uid NOT IN ( 0, 3, 4 ) 

    --public, INFORMATION_SCHEMA, system_function_schema 

    IF @newuid IS NULL 

      BEGIN 

          EXECUTE Sp_msadduser_implicit_ntlogin 

            @newowner 

          SELECT @newuid = uid 

          FROM   sysusers 

          WHERE  NAME = @newowner 

                 AND isaliased = 0 

                 AND NAME public 

      END 

    IF @newuid IS NULL 

      BEGIN 

          RAISERROR(15410,-1,-1,@newowner) 

          RETURN ( 1 ) 

      END 

    -- CHECK IF CHANGING OWNER OF OBJECT OR ITS CHILDREN WOULD PRODUCE A DUPLICATE 

    IF EXISTS (SELECT * 

               FROM   sysobjects 

               WHERE  uid = @newuid 

                      AND NAME IN (SELECT NAME 

                                   FROM   sysobjects 

                                   WHERE  id = @objid 

                                           OR parent_obj = @objid)) 

      BEGIN 

          RAISERROR(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 (theyll be incorrect with new owner) -- 

    DELETE syspermissions 

    WHERE  id = @objid 

    UPDATE sysobjects 

    SET    uid = @newuid 

    WHERE  id = @objid 

    UPDATE sysobjects 

    SET    uid = @newuid 

    WHERE  parent_obj = @objid 

    COMMIT TRANSACTION 

    RETURN 0 -- sp_changeobjectowner 

go 

高危预警| SQL数据库成主要攻击对象，或引发新一轮大规模勒索 云安全中心已提供免费7天试用服务，登陆控制台开启企业版试用功能，利用漏洞修复、基线检查、安全告警功能对系统做深度检查，及时修复当前存在的安全隐患。
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.12. 其他数据库对象 5.12. 其他数据库对象 表是一个关系型数据库结构中的核心对象，因为它们承载了我们的数据。但是它们并不是数据库中的唯一一种对象。有很多其他种类的对象可以被创建来使得数据的使用和刮泥更加方便或高效。
SQL Server修改数据库对象所有者(Owner)浅析 原文:SQL Server修改数据库对象所有者(Owner)浅析 在SQL Server数据库中如何修改数据库对象（表、视图、存储过程..）的所有者（Owner）呢？一般我们可以使用系统提供的系统存储过程sp_changeobjectowner来修改。
SQL Server中授予用户查看对象定义的权限 原文:SQL Server中授予用户查看对象定义的权限 SQL Server中授予用户查看对象定义的权限   在SQL Server中，有时候需要给一些登录名（用户）授予查看所有或部分对象（存储过程、函数、视图、表）的定义权限存。
在SQL Server中查看对象依赖关系 原文 在SQL Server中查看对象依赖关系 Viewing object dependencies in SQL Server Deleting or changing objects may affect other database objects like views or ...
如何快速搜索SQL数据库数据和对象 原文 如何快速搜索SQL数据库数据和对象 Frequently, developers and DBAs need to search databases for objects or data. If you’d ever searched for a database function tha...
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载