开发者社区> 杰克.陈> 正文

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

简介: 原文:SQL Server修改数据库对象所有者(Owner)浅析 在SQL Server数据库中如何修改数据库对象(表、视图、存储过程..)的所有者(Owner)呢?一般我们可以使用系统提供的系统存储过程sp_changeobjectowner来修改。
+关注继续查看
原文: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;
 
go
 
exec sp_changeobjectowner '[db_owner].[T1]','dbo';
 

 

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

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

use test;
 
go
 
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')

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

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

USE Test;
GO
 
CREATE TABLE [db_owner].T1
(
 ID   INT ,
 NAME VARCHAR(20)
);
 
CREATE VIEW [db_owner].V_T1
AS
  SELECT * FROM T1;
 
CREATE VIEW [db_owner].V_T2
AS
  SELECT * FROM db_owner.T1;
 
CREATE PROCEDURE PRC_TEST_ONE
AS
 SELECT * FROM T1;
 
GO
 
CREATE PROCEDURE PRC_TEST_TWO
AS
 SELECT * 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 T1
FOR [db_owner].T1
GO
 
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 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_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 (they'll 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 

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

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

    ALTER SCHEMA dbo TRANSFER db_owner.T1;

    GO

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
javaWeb初识 ServletContext全局对象,过滤器,监听器
ServletContext全局对象: import javax.servlet.ServletConfig; import javax.servlet.ServletContext; import javax.
739 0
利用Object.defineProperty挂接set钩子,监控对象属性的修改事件
利用Object.defineProperty挂接set钩子,监控对象属性的修改事件
37 0
第11章—使用对象关系映射持久化数据—SpringBoot+SpringData+Jpa进行查询修改数据库
SpringBoot+SpringData+Jpa进行查询修改数据库 JPA由EJB 3.0软件专家组开发,作为JSR-220实现的一部分。但它又不限于EJB 3.0,你可以在Web应用、甚至桌面应用中使用。
1119 0
使用ADO对象添加、修改、删除数据
使用ADO对象对数据库中的数据进行添加、修改和删除等操作。首先创建一个ADO类,通过ADO类连接数据库,并打开记录集。例如,使用ADO对象添加、修改、删除数据,程序设计步骤如下:(1)创建一个基于对话框的应用程序,将对话框的Caption属性修改“使用ADO对象添加、修改、删除数据”。
855 0
MySQL---数据库从入门走向大神系列(十)-Connection对象池、装饰模式与动态代理模式
问题概述: 之前本系列博客写的,全部都是一个connection对象,不知道大家发现没有,我们既然做了一个Connection工具类,那么大家肯定都是从那里面拿Connection对象的,之前的如果是多线程运行,很容易出问题的,你想想事务处理就知道了,同时用事务处理操作同一个Connection,肯定会出问题的。
906 0
SQL Server修改表结构后批量更新所有视图
最近修改了数据库表结构,数据同步的时候出了问题,发现很多数据明明已经修改,但是通过视图筛选出来的还是原来的数据,所以怀疑应该是视图缓存了数据,在园子里找到下面的博文,在这里做个记录备忘。   原文链接:http://www.cnblogs.com/yashen/archive/2004/12/23/81000.html   我们在使用SqlServer时经常遇到这种情况,当修改某个表的结构后,相关的视图就不对了而导致程序错误,因此就有个下面这个存储过程。
961 0
二、修改数据库的全局名称
二、修改数据库的全局名称 1、查看当前数据库的全局名称如下: SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- WWL.
654 0
hmailserver 通过 webmail 修改密码
Password changehmailserver是一种流行的免费邮件服务器窗口 hmailserver是一种流行的免费邮件服务器软件,通过afterlogic Webmail可以实现理想的webmail功能。
2192 0
【转】MSSQL中数据库对象类型解释--觉得很好,自己存档
出处:http://blog.ueren.com/?tag=sysobjects public string GetObjectTypeName(object oType){switch (oType+""){case "U":return "用户表";case "V":retu...
713 0
mysql 5.7 修改数据库表
修改数据表 修改数据表是修改数据库中已经存在的数据表的结构。MySQL使用alter table 语句修改表。常用的修改表操作有:修改名,修改字段数据类型或字段名,增加和删除字段,修改字段的排列位置,更改表的存储引擎,删除表的外键约束。
1143 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载