데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

인증으로부터 분리된 사용자 방지

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 관리자가 겪는 문제 중 하나가 인증 관련문제로 '고아'가 발생하는 경우이다. SQL Server에서 SQL Server 로그인을 만들 때 문제가 발생 한다. 이 작업을 수행 할 때 SQL Server는 SQL Server 로그인에 대해 고유 SID를 생성한다. 로그인을 생성한 후 해당 서버 인스턴스의 사용자 데이터베이스에서 데이터베이스 사용자를 작성하고 해당 SQL Server 로그인을 사용하여 데이터베이스 사용자를 연결 한다.

 

다른 SQL Server 인스턴스에 해당 사용자 데이터베이스를 복원하려고 할 때 까지는 잘 작동하다가 서버에 이전과 동일한 사용자 ID를 가진 SQL Server 로그인을 만들 경우 해당 SQL Server 로그인의 SID(다른 데이터베이스 인스턴스)는 복원 한 사용자 데이터베이스에서 데이터베이스 사용자가 일치하지 않는다.

 

데이터베이스 미러링을 사용하는 경우 데이터베이스 사용자가 고아인 경우 하나의 인스턴스에서 다른 인스턴스로 페일 오버 되기 때문에 이것은 특히 큰 문제가 된다. 이 외에도 로그전달 등 기존 데이터베이스에서 새 데이터베이스 서버로 마이그레이션 할 때 문제가 발생 한다.

 

이 문제를 해결 하기 위한 방법은 여러 가지가 있지만 여기서는 원본 서버의 SID를 이용하여 같은 SID의 새 서버 로그인을 만드는 것이다.

 

다음 스크립트는 현재 서버의 모든 SQL Server 로그인에 대한 SID 정보를 확인 할 수 있다.

SELECT name, [sid]

FROM sys.server_principals

WHERE [type] = 's';

 

 

 

기존 계정을 삭제하고 새로운 계정 생성시 SID를 부여하여 생성한다.

-- Create new SQL Login on new server instance

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLTest')

DROP LOGIN SQLTest;

GO

 

-- Use the sid from the old server instance

CREATE LOGIN SQLTest WITH PASSWORD = N'YourStrongPassword#', sid = 0x05130637A7E8104FBB8E13106405138B,

DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO

 

 

 

[참고자료]

http://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-avoid-orphaned-database-users-with-sql-server-authentication

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1557
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1731
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4577
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1730
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1559
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1540
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7812
» 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2180
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1242
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1771
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1780
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1472
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1859
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1648
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1392
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1161
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2507
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421





XE Login