[MSSQL] Enable 계정 및 각 계정별 발급일, PW 변경일 확인

DB admin으로서, 각 계정이 언제 생성되었는지, PW가 언제 변경되었는지 확인해야 되는 상황이 생길 수 있다.

그런 상황에서 유용한 sys.server_principals(T-SQL) 을 활용하여 원하는 정보를 쿼리로 획득해보자!

 

먼저 현재 SQL Server에서 활성화된 계정을 확인할 필요가 있다.

1. MSSQL 상에서 Enabled 되어 있는 계정 확인

SELECT *
FROM sys.server_principals
WHERE is_disabled = 0 AND type in ('S')

sys.server_principals SELECT 결과

• name : 보안 주체의 이름. 서버 내에서 고유. (sysname 데이터타입)
• principal_id : 보안 주체의 ID 번호. 서버 내에서 고유. (int 데이터 타입)
• sid : 보안 주체의 SID(보안 ID). Windows 보안 주체의 경우 Windows SID와 일치. (varbinary(85) 데이터타입)
• type : 보안 주체 유형. (char(1) 데이터타입)
  - S : SQL 로그인
  - U : Windows 로그인
  - G : Windows 그룹
  - R : 서버 역할(Role)
  - C : 인증서에 매핑된 로그인
  - E : Azure Active Directory 에서 외부 로그인
  - X : Azure Active Directory 그룹 또는 애플리케이션의 외부 그룹
  - K : 비대칭 키에 매핑된 로그인
• type_desc : 보안 주체 유형에 대한 설명. 값은 아래 중에 하나.
  SQL_LOGIN / WINDOWS_LOGIN / WINDOWS_GROUP / SERVER_ROLE / CERTIFICATE_MAPPED_LOGIN / EXTERNAL_LOGIN / EXTERNLA_GROUP / ASYMMETRIC_KEY_MAPPED_LOGIN
• is_disabled : 1=로그인할 수 없는 상태 / 0=로그인 가능한 상태
• credential_id : 이 보안 주체와 연결된 자격증명의 ID. 이 보안 주체와 연결된 자격 증명이 없는 경우 NULL 이 된다.

 

---

2. MSSQL Enable된 계정의
Default 데이터베이스, 최초 생성일, 계정 관련 변경일, PW 변경일, 해당 PW 사용일 수
확인 쿼리

SELECT NAME AS id
, default_database_name AS 기본_데이터베이스
, create_date AS 최초_생성일
, modify_date AS 계정설정_변경일
, LOGINPROPERTY(NAME, 'PasswordLastSetTime') AS 패스워드_변경일
, DATEDIFF(DD, CONVERT(SMALLDATETIME, LOGINPROPERTY(name, 'PasswordLastSetTime')), GETDATE()) AS 패스워드_사용일수
FROM sys.server_principals
WHERE is_disabled = 0 AND type in ('S')

sys.server_principals 를 통한 패스워드 변경일 조회 결과

위와 같이 각 계정별 최초 생성일 및 패스워드 변경일 등의 유용한 정보를 쿼리를 통해 확인할 수 있다!

---

참고

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=kdj0876&logNo=221631226899 

MSSQL 활성화 계정 및 비밀번호 변경 이력 확인

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql?view=sql-server-ver16 

sys.server_principals(Transact-SQL) - SQL Server