MSSQL DB 복원후 사용자 및 스키마 일괄 변경 스크립트

MSSQL에서 타서버로의 DB 복원후 계정 문제로 인하여 로그인이 않되는 경우가 많습니다.

이는 기존 백업파일내의 사용자와 현시스템의 DB의 사용자가 일치화 되지 않아 생기는 경우 입니다.

이에 대하여 간략히 수정할수 있는 방법이 있습니다.

 

유저에 대한 일괄변경

use DB명

go
exec sp_change_users_login 'Auto_Fix','ID명',NULL,'패스워드';
go

 

 

복원후 테이블에 대한 기존 스키마가 있어 이를 변경하고자할경우

(MSSQL 2005기준으로 설명합니다.)

 

use DB명

go

declare @sql as nvarchar(100);
declare @tbname as nvarchar(100);
declare c cursor fast_forward for
select [name] from sysobjects where xtype ='u' or xtype ='p' order by name;
open c;
fetch next from c into @tbname;

while @@fetch_status=0
begin
 set @sql='alter schema dbo transfer 스키마명.'+@tbname;
 exec(@sql);
 fetch next from c into @tbname;
end
close c;
deallocate c;

 

 

위에 보이는 xtype은 스키마가 가지고 있는 오브젝트 타입을 의미하며 아래와 같은 정보와 같습니다.

xtype

C = check 제약 조건
D = 기본값 또는 default 제약조건
F = FOREIGN KEY 제약 조건
L = 로그
FN = 스칼라함수
IF = 인라인 테이블 함수
P = 저장프로시저
PK = PRIMARY KEY 제약 조건(유형은 K)
RF = 복제 필터 저장 프로시저
S = 시스템 테이블
TF = 테이블 함수
TR = 트리거
U = 사용자 테이블
UQ = UNIQUE 제약 조건(유형은 K)
V = 뷰
X = 확장 저장 프로시저

 

다양한 타입으로 조회하여 수정할수 있습니다.

(where xtype ='u' or xtype ='p' or xtype = 'v')

뷰타입,사용자테이블,저장프로시저를 조회하는 구문으로 수정하여 사용하시기 바랍니다.

 

감사합니다.