AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2016-04-26 2453
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介：

在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文档所描述的，系统存储过程的使用非常简单，如下所示

clipboard

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';

clipboard[1]

如果存在对应表的同义词，那么使用系统存储过程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';

clipboard[2]

 

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

文章标签：
云数据库 RDS SQL Server 版
数据库
存储
SQL
Windows
Go
安全
关键词：
数据库sql
SQL数据库
SQL对象
sql数据库对象
数据库对象
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
潇湘隐者
目录
相关文章
Apache_Tomcat_Nignx
|
13天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因，包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题，并提供了详细的解决步骤，如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等，确保导入过程顺利。
Apache_Tomcat_Nignx
47 3
运维有小邓
|
12天前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制，旨在检测潜在威胁和漏洞，监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息，帮助管理员追踪数据库中的特定活动，确保数据安全和合规性。SQL Server审核分为服务器级和数据库级，涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警，帮助快速响应安全事件。
运维有小邓
30 1
小王老师呀
|
24天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第16天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤，包括配置系统源、安装 SQL Server 2019 软件包以及数据库初始化，确保 SQL Server 正常运行。
小王老师呀
38 4
小王老师呀
|
1月前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤，包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤，您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
小王老师呀
71 1
小王老师呀
|
1月前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第7天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤，包括系统要求检查与准备、配置安装源、安装 SQL Server 2019、配置 SQL Server 以及数据库初始化（可选）。通过这些步骤，你可以成功安装并初步配置 SQL Server 2019，进行简单的数据库操作。
小王老师呀
42 1
陌陌谣
|
1月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件：PL/SQL 实现的数据库关联监控
在当今网络环境中，基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块，能够提供全面而准确的监控信息，帮助管理者有效监督局域网内的电脑使用情况。
陌陌谣
22 2
游客ny3yjjsyp7iro
|
21天前
|
SQL Java 数据库连接
canal-starter 监听解析 storeValue 不一样，同样的sql 一个在mybatis执行 一个在数据库操作，导致解析不出正确对象
canal-starter 监听解析 storeValue 不一样，同样的sql 一个在mybatis执行 一个在数据库操作，导致解析不出正确对象
游客ny3yjjsyp7iro
38 0
1436047922066202
|
2月前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库，有1000多个文件，大小几十TB。数据库每10天生成一个NDF文件，每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏，数据库不可用。管理员试图恢复数据库，发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB，但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件，然后修复数据库。
1436047922066202
38 3
游客g6r3tt6lma5xg
|
2月前
|
SQL 数据库
SQL数据库基础语法入门
[link](http://www.vvo.net.cn/post/082935.html)
游客g6r3tt6lma5xg
34 0
eric0435
|
SQL
动态sql语句来删除用户下的对象
eric0435
1574 0

热门文章

最新文章

  • 1
    多台SQLServer数据实时同步，syncnavigator使用
  • 2
    阿里云服务器怎么搭建sql server（即 MS SQL）？
  • 3
    SQLServer CDC数据通过Kafka connect实时同步至分析型数据库 AnalyticDB For PostgreSQL及OSS
  • 4
    使用批处理文件命令行方式快速启动和停止IIS、SqlServer
  • 5
    数据库SQL Server与C#中数据类型的对应关系
  • 6
    SQL Server 查询性能优化 相关文章
  • 7
    SQLSERVER数据库分离作用及附加方法
  • 8
    第十二章——SQLServer统计信息（2）——非索引键上统计信息的影响
  • 9
    SQL Server: Get table primary key and Foreign Key using sql query
  • 10
    SQL Server Management Studio Express菜单中文下面是英文的问题
  • 1
    云原生一体化数据库技术是一个具有潜力的领域
    114
  • 2
    构建高性能数据库的秘诀
    95
  • 3
    新一代数据库技术：融合AI的智能数据管理系统
    325
  • 4
    新一代数据库技术：融合人工智能与分布式系统的未来前景
    352
  • 5
    使用 Python 访问数据库的基本方法
    66
  • 6
    数据库解决方案
    102
  • 7
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    161
  • 8
    服务器Linux系统配置mysql数据库主从自动备份
    214
  • 9
    SQLite数据库介绍以及安装
    102
  • 10
    sprinboot+vue集成neo4j图数据库
    171

    • 相关课程

    更多
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 快速体验PolarDB开源数据库
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。