修改数据库中表和存储过程的所有者
来源:互联网 发布:c语言做网站 编辑:程序博客网 时间:2024/06/04 00:23
修改数据库中表和存储过程的所有者
建立下面两个存储过程
在VS2003里运行第一个changename,输入的参数‘原来的用户名’,‘dbo’
再运行第二个存储过程ChangeObjectOwner,输入的参数‘原来的用户名’,‘dbo’
方法二(没试验过)
建立以下两个存储过程
以SA登陆查询分析器 ,选中你要的数据库
执行存储过程
执行exec Changename '原所有者','dbo'
执行exec ChangeObjectOwner '原所有者','dbo'
///////////////////////////////////////////
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[changename]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[changename]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create PROCEDURE dbo.changename
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
///////////////////////////////////////////
CREATE PROCEDURE dbo.ChangeObjectOwner
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
建立下面两个存储过程
在VS2003里运行第一个changename,输入的参数‘原来的用户名’,‘dbo’
再运行第二个存储过程ChangeObjectOwner,输入的参数‘原来的用户名’,‘dbo’
方法二(没试验过)
建立以下两个存储过程
以SA登陆查询分析器 ,选中你要的数据库
执行存储过程
执行exec Changename '原所有者','dbo'
执行exec ChangeObjectOwner '原所有者','dbo'
///////////////////////////////////////////
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[changename]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[changename]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create PROCEDURE dbo.changename
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
///////////////////////////////////////////
CREATE PROCEDURE dbo.ChangeObjectOwner
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
- 修改数据库中表和存储过程的所有者
- 修改数据库中表的所有者.
- 更新数据库中表、存储过程或者所有对象的所有者
- 修改表和存储过程的所有者
- 修改数据库中表的所有者为dbo。
- MS-SQL2000中修改表和存储过程的所有者
- sql server 批量修改表和存储过程的所有者
- MSSQL中批量修改数据表和存储过程的所有者
- sql server 批量修改表和存储过程的所有者
- 修改MS SQL2K数据库中表的所有者及其它
- 批量修改表、存储过程的所有者
- 批量修改存储过程的所有者
- sql server 批量修改表和存储过程的所有者——提高SQL安全性
- 批量修改 SQL Server 表、存储过程的所有者
- MySQL修改存储过程或者函数所有者
- sqlserver修改数据库的所有者
- MSSQL 2000更改表和存储过程的所有者
- sqlserver2005如何更改存储过程和表的所有者
- apt-cache 及Dpkg 指令操作快速参考
- Programming is like sex
- Shell读取文件内容
- 数据库中查找替换某个文字
- contains在SQL语句里面的作用, 在asp里面写一个sql语句,使得可以遍历全部字段的搜索
- 修改数据库中表和存储过程的所有者
- ubuntu下挂载win分区快速配置
- 上个月第一天,最后一天
- 建立临时表
- SQL/PLUS基础
- 构造函数和析构函数中的虚函数调用
- 简单的通讯数据管理(java)
- 把列排成行
- 求两个float变量的商