左岸印象

更改Mssql2000数据库拥有对象

分类: 服务器类 作者: 阅读: 2,153        暂无评论

IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL  //line continous
                  DROP PROC ChangeAllObjOwner
GO

上面貌似可以不加进去,直接复制中间的那大段到查询分析器:

查询分析器中输入下面代码查询执行
——————————————————————————————————————

CREATE PROCEDURE ChangeAllObjOwner (
  @oldowner sysname,
  @newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON
 
--check that the @oldowner exists in the database
IF USER_ID(@oldowner) IS NULL
  BEGIN
    RAISERROR ('The @oldowner passed does not exist in the database',
16, 1)
    RETURN
  END
--check that the @newowner exists in the database
IF USER_ID(@newowner) IS NULL
  BEGIN
    RAISERROR ('The @newowner passed does not exist in the database',
 16, 1)
    RETURN
  END
 
DECLARE owner_cursor CURSOR FOR
  SELECT name FROM sysobjects WHERE uid = USER_ID(@oldowner)
 
OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
  SET @objname = @oldowner + '.' + @objname
  EXEC sp_changeobjectowner @objname, @newowner
  FETCH NEXT FROM owner_cursor INTO @objname
END
 
CLOSE owner_cursor
DEALLOCATE owner_cursor
GO

———————————————————————————————————————
上一步执行完后输入下面代码执行
oldowner改成旧的用户名,newowner改成dbo

EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex'
*/

标签: , , ,

转载注明: 转自左岸印象

本站遵循: 署名-非商业性使用-禁止演绎 3.0 共享协议

文章分享: Digg / Twitter / Facebook / GReader / 鲜果 / 豆瓣 / 人人网 / 开心网 / 新浪微博



发表评论




XHTML: 您可以使用如下代码:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">