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
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
音视频技术开发周刊 61期
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/vn9PLgZvnPs1522s82g/article/details/81463963 『音视频技术开发周刊』由LiveVideoStack团队出品,专注在音视频技术领域,纵览相关技术领域的干货和新闻投稿,每周一期。点击『阅读原文』,浏览第61期内容,祝您阅读愉快。 架构 马思伟:视频领域是个海洋,可以游泳、冲浪、潜水和远航 6月,在北京大学理科2号楼一间办公室内见到了北京大学信息科学技术学院教授马思伟,并相约这次邮件采访。作为国产编解码器AVS视频组的组长,马思伟畅谈了编码器的格局,AVS的机会以及学习编解码的经验及方法。 何亚明:Facebook的工具文化和多媒体QoE 本文来自 阿里巴巴 信息平台资深技术专家 何亚明在LiveVideoStackCon 2018热身分享,并由LiveVideoStack整理而成。在分享中,何亚明介绍了Facebook工程师团队的特点与其常用的几种开发工具,并对其开发流程与实际测试方法进行了总结。 百家云直播系统技术演进历程 本文来自百家云技术总监张...
- 下一篇
大数据平台的建设目标
它山之石,可以攻玉——要谈大数据平台的建设目标,首先要知道业界先进的实践经验,了解别人的数据平台是怎样的,然后才能结合自己公司的实际情况设定合适的目标和方向。 1 别人的大数据平台是怎样的 那么,别人的大数据平台是怎样的呢?如果参加过一些大大小小的技术分享论坛或会议,你应该不难发现,在各种各样新的诸如“×××公司大数据平台实践无敌干货分享”之类的PPT中,谈到大数据平台的技术组件时,多半都会给出一个大同小异的系统架构图。 在这个架构图中,各种日志和DB数据采集组件、存储和计算引擎、监控和调度系统,不管在实践中真实的应用情况如何,反正在图上所有组件一个都不缺,除了个别组件的增减替换,每家公司的大数据平台看起来都没有太大的区别。 所以,如果你要问大数据平台的基础架构图长什么样,不用自己画,直接用HortonWorks公司的HDP发行版套件图来展示,估计也没啥大的不妥,如下图所示。 除了各种公开会议,过去的几年里我和北上杭的不少大数据平台从业者也常常有各种私下的交流,在交流的过程中,讨论到大数据平台的建设方向的时候,也有些人很直白地和我说:别折腾了,大数据平台建设的整体思路其实都差不多,随便...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS8编译安装MySQL8.0.19
- Windows10,CentOS7,CentOS8安装Nodejs环境
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- SpringBoot2整合Redis,开启缓存,提高访问速度
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Docker安装Oracle12C,快速搭建Oracle学习环境