12604.htm

来自「一本很基础的SQL讲解」· HTM 代码 · 共 28 行

<link href="./dzs_cs.css" rel="stylesheet" type="text/css" />
<table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="24" align="center" valign="bottom" class="d_font3">更改数据库中表的所属用户的两个方法</td>
      </tr>
      <tr>
        <td height="3" bgcolor="#E3E3E3"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td class="d_font4">更改某个表<BR>exec sp_changeobjectowner 'tablename','dbo'
<P>存储更改全部表<BR>CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch<BR>&nbsp;@OldOwner&nbsp;as NVARCHAR(128),<BR>&nbsp;@NewOwner&nbsp;as NVARCHAR(128)<BR>AS</P>
<P>DECLARE @Name&nbsp; &nbsp;as NVARCHAR(128)<BR>DECLARE @Owner&nbsp;&nbsp;as NVARCHAR(128)<BR>DECLARE @OwnerName&nbsp;&nbsp;as NVARCHAR(128)</P>
<P>DECLARE curObject CURSOR FOR <BR>&nbsp;select&nbsp;'Name'&nbsp;&nbsp;&nbsp;= name,<BR>&nbsp;&nbsp;'Owner'&nbsp;&nbsp;&nbsp;= user_name(uid)<BR>&nbsp;from sysobjects<BR>&nbsp;where user_name(uid)=@OldOwner<BR>&nbsp;order by name</P>
<P>OPEN&nbsp; curObject<BR>FETCH NEXT FROM curObject INTO @Name, @Owner<BR>WHILE(@@FETCH_STATUS=0)<BR>BEGIN&nbsp;&nbsp;&nbsp; &nbsp;<BR>&nbsp;if @Owner=@OldOwner <BR>&nbsp;begin<BR>&nbsp;&nbsp;set @OwnerName = @OldOwner + '.' + rtrim(@Name)<BR>&nbsp;&nbsp;exec sp_changeobjectowner @OwnerName, @NewOwner<BR>&nbsp;end<BR>--&nbsp;select @name,@NewOwner,@OldOwner</P>
<P>&nbsp;FETCH NEXT FROM curObject INTO @Name, @Owner<BR>END</P>
<P>close curObject<BR>deallocate curObject</P>
<P>GO</P></td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;</td>
      </tr>
    </table>