MSSQL 로그인 계정이 접근할 수 있는 데이터베이스 확인

1. 로그인 계정이 접근할 수 있는 데이터베이스 확인

(1) 테스트를 위한 로그인 계정 생성

① sysadmin. 모든 데이터베이스 엔진 작업을 수행할 수 있습니다.

USE [master]; GO CREATE LOGIN boss WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE sysadmin ADD MEMBER boss;

② serveradmin. 서버 차원의 구성 옵션을 변경하고 서버를 종료할 수 있습니다.

CREATE LOGIN dev1 WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE serveradmin ADD MEMBER dev1;

 

③ dbcreator. 데이터베이스를 생성, 변경, 삭제, 복원할 수 있습니다.

CREATE LOGIN dev2 WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE dbcreator ADD MEMBER dev2;

④ public. 모든 SQL Server 사용자, 그룹 및 역할은 기본적으로 public 고정 서버 역할에 속합니다.

CREATE LOGIN peon1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;   CREATE LOGIN peon2 WITH PASSWORD = 'x', CHECK_POLICY = OFF; GO   USE [codefactoryglobal2024]; GO   CREATE USER codefactoryglobal FROM LOGIN codefactoryglobal; GO

 

(2) 생성된 계정이 접근할 수 있는 데이터베이스를 확인

① SSMS에서 codefactoryglobal 로 로그인 하여 특정 데이터베이스를 접근한다. 데이터베이스가 보이지만 접근은 되지 않는다.

 

② codefactoryglobal 계정에 대해 모든 데이터베이스 거부를 설정하면 데이터베이스 목록을 확인 할 수 없다.

USE [master]; GO DENY VIEW ANY DATABASE TO codefactoryglobal;

 

 

③ 다음 스크립트는 특정 계정에 대해 접근할 수 있는 모든 데이터베이스에 대한 액세스를 확인 할 수 있다.

EXECUTE AS LOGIN = N'codefactoryglobal'; GO SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1; GO REVERT;

 

(3) SQL Server에 생성된 전체 계정에 대하여 접근할 수 있는 데이터베이스 목록을 확인

-- Table for collecting data CREATE TABLE #TmpTable(LoginID SYSNAME, DBName SYSNAME, ErrorMsg INT); -- Table of logins we care about today DECLARE @Logins TABLE(LoginID SYSNAME); INSERT @Logins([LoginID]) VALUES(N'boss'), (N'codefactoryglobal'), (N'codefactoryglobal2'); -- Build SQL commands for read attempts to each online db DECLARE @v_SQL NVARCHAR(MAX) = N'', @v_Cmd NVARCHAR(MAX) = N''; SELECT @v_SQL += N' BEGIN TRY     INSERT #TmpTable SELECT TOP (1) SUSER_SNAME(), N''' + REPLACE([name], '''', '''''') + N''', 0 FROM ' + QUOTENAME(name) + N'.sys.all_objects; END TRY BEGIN CATCH     INSERT #TmpTable SELECT SUSER_SNAME(), N''' + REPLACE([name], '''', '''''') + ''', ERROR_NUMBER(); END CATCH;'  FROM sys.databases WHERE [state] = 0; SELECT @v_Cmd += N' EXECUTE AS LOGIN = N''' + REPLACE([LoginID], '''', '''''') + N'''; ' + @v_SQL + N'REVERT;'  FROM @Logins; EXEC [master].sys.sp_executesql @v_Cmd; -- Report SELECT [LoginID], [DBName], [Access?] = (CASE ErrorMsg WHEN 0 THEN 'Yes' WHEN 229 THEN 'Access denied on sys.all_objects' WHEN 916 THEN 'Cannot connect to database' ELSE 'No - ' + CONVERT(VARCHAR(11), ErrorMsg)  END) FROM #TmpTable  ORDER BY [LoginID], [DBName]; GO DROP TABLE #TmpTable;