Linked DB를 이용하여 DDL문 사용하기

MS-SQL에서 일반적으로 linked db를 이용하여 다른 DB를 자신의 DB에서 처럼 사용할 수 있다.

일반적인 DML(insert, update, delete)는 별 문제 없이 사용할 수 있지만, DDL(create, drop, truncate)등은 사용할 때 권한 문제가 발생한다.

필요한 권한은 sysadmin 고정 서버 역할 및 db_owner 및 db_ddladmin 고정 데이터베이스 역할의 기본 권한들이다.

문제는 연결한 계정이 권한을 가지고 있어도(SA 권한으로 연결) 실행이 안되고 에러가 발생한다.

다음과 같이 하면 DDL문을 실행할 수 있다.

Use master;
Go
ALTER SERVICE MASTER KEY FORCE REGENERATE;
Go

이는 SQL 2005부터 추가된 SERVICE MASTER KEY 때문에 발생하는 것으로 master DB에 대한 보안이 강화되어 발생하는 문제이다.

서비스 마스터 키는 연결된 서버 암호, 인증서 또는 데이터베이스 마스터 키를 처음으로 암호화할 필요가 있을 때 자동으로 생성됩니다. 서비스 마스터 키는 로컬 시스템 키 또는 Windows 데이터 보호 API를 사용하여 암호화됩니다. 이 API는 SQL Server 서비스 계정의 Windows 자격 증명으로부터 파생된 키를 사용합니다.

서비스 마스터 키의 암호는 해당 키가 만들어진 서비스 계정이나 해당 서비스 계정의 Windows 자격 증명에 대한 액세스 권한이 있는 보안 주체에 의해서만 해독될 수 있습니다. 따라서 SQL Server 서비스를 실행 중인 Windows 계정을 변경하면 서비스 마스터 키의 암호 해독도 새 계정으로 활성화해야 합니다.

라고 되어 있다. 보안이 강화 된 것은 좋은데 DB 장비 교체에 따라 DB를 이전할 때 고려해야만 하는 사항이 더 늘었다.

기존에 master DB만 백업을 하였는데 추가로 SERVICE MASTER KEY 를 백업해야만 완벽한 이전 복원이 가능해 졌다. KEY를 백업 복원하는 방법은 다음과 같다. 

BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key' ENCRYPTION BY PASSWORD = 'password' 
RESTORE SERVICE MASTER KEY FROM FILE = 'c:\service_master_key' DECRYPTION BY PASSWORD = 'password'

ALTER SERVICE MASTER KEY FORCE REGENERATE;

는 기존 키를 무시하고 새로 생성하는 방법이다.

master DB를 복사하여 기동하였을 때 기존 linked db를 사용할 수 없고 삭제도 안될 것이다. 이 때 키를 초기화 하고 삭제하는 방법으로 처리가 가능하지 않을까.

해 보지 않아서 이부분은 잘 모르겠다.