로컬서버에  backup받았던  .bak파일로..

다른  서버에서  복원하는  방법은.

1.  똑같은  디비명으로  디비를  생성하고

2.  풀백업을  한후

3.  똑  같은  파일명으로  엎어치면  된다.



하지만  이때  문제가  발생하는데..

사용자명이  같아도  서버가  틀리면  다르게  인식해서..

로그인이  안되는  문제가  발생한다.



이때에는..  

일단  소유자를  dbo로  바꾼후..

특정  소유자를  다시  만들어  그  디비의  테이블들을  그  소유자로  매칭시켜주면

된다.



그때  쓸수  있는  프로시저를  아래에  적었놓았다.

유용하게  쓰시길



/*

Version:  SQL  Server  7.0/2000

Created  by:  Alexander  Chigrik

http://www.MSSQLCity.com/  -  all  about  MS  SQL

(SQL  Server  Articles,  FAQ,  Scripts,  Tips  and  Test  Exams).



This  stored  procedure  can  be  used  to  run  through  all  of  a  specific  

database's  objects  owned  by  the  'oldowner'  and  change  the  old

owner  with  the  new  one.

You  should  pass  the  old  owner  name  and  the  new  owner  name,  

as  in  the  example  below:



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

*/



IF  OBJECT_ID('ChangeAllObjOwner')  IS  NOT  NULL  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

==========================================

쓰는  명령어는

EXEC  ChangeAllObjOwner  @oldowner  =  'dbo',  @newowner  =  'kdemo'

날짜: 2003-07-25 12:11:29, 조회수: 2795