《更改数据库中表的所属用户两种方法》由会员分享,可在线阅读,更多相关《更改数据库中表的所属用户两种方法(2页珍藏版)》请在金锄头文库上搜索。
1、更改数据库中表的所属用户两种方法更改数据库中表的所属用户两种方法大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都 不能打开了,原因是建表的时候采用了当时的数据库用户 -更改某个表 exec sp_changeobjectowner tablename,dbo -存储更改全部表 CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch OldOwner as NVARCHAR(128), NewOwner as NVARCHAR(128) AS DECLARE Name as NVARCHAR(128) DECLARE Owner as
2、 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 - select name,NewOwner,OldOwner FETCH NEXT FROM curObject INTO Name, Owner END close curObject deallocate curObject GO此文章出自达思 SQL 数据库修复软件 http:/ http:/