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

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

 

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

 

SQL Server 로그인 계정은 특정한 데이터베이스에 접근할 수 있다. 생성된 로그인 계정이 접근 할 수 있는 데이터베이스를 확인하는 방법에 대해서 알아본다.

 

실습을 위해 로그인 계정을 생성한다.

USE [master]; GO   -- add "boss" to sysadmin: CREATE LOGIN boss WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE sysadmin ADD Member boss;   -- add "dev1" to serveradmin: CREATE LOGIN dev1 WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE serveradmin ADD Member dev1;   -- add "dev2" to dbcreator: CREATE LOGIN dev2 WITH PASSWORD = 'x', CHECK_POLICY = OFF; ALTER SERVER ROLE dbcreator ADD Member dev2;   -- "peon1" will only be in public CREATE LOGIN peon1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;   -- "peon2" will be in public *and* granted explicit access to AW2014: CREATE LOGIN peon2 WITH PASSWORD = 'x', CHECK_POLICY = OFF; GO   USE [AdventureWorksDW2012]; GO CREATE USER peon2 FROM LOGIN peon2; GO

 

 

 

생성된 계정이 접근할 수 있는 데이터베이스를 확인하기 위해 SSMS에서 Peon1로 로그인 하여 특정 데이터베이스를 접근한다. 데이터베이스가 보이지만 접근은 되지 않는다.

 

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

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

 

 

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

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

 

 

다음 스크립트는 SQL Server에 생성된 전체 계정에 대하여 접근할 수 있는 데이터베이스 목록을 확인 할 수 있다.

-- table for collecting data   CREATE TABLE #x([login] SYSNAME, db SYSNAME, error INT);   -- table of logins we care about today DECLARE @logins TABLE([login] SYSNAME); INSERT @logins([login]) VALUES(N'boss'),(N'peon1'),(N'peon2');   -- build SQL commands for read attempts to each online db DECLARE @sql NVARCHAR(MAX) = N'', @cmd NVARCHAR(MAX) = N'';   SELECT @sql += N' BEGIN TRY INSERT #x SELECT TOP (1) SUSER_SNAME(),N''' + REPLACE(name,'''','''''') + N''',0 FROM ' + QUOTENAME(name) + N'.sys.all_objects; END TRY BEGIN CATCH INSERT #x SELECT SUSER_SNAME(),N''' + REPLACE(name,'''','''''') + ''', ERROR_NUMBER(); END CATCH;' FROM sys.databases WHERE [state] = 0;   SELECT @cmd += N' EXECUTE AS LOGIN = N''' + REPLACE([login], '''', '''''') + N'''; ' + @sql + N' REVERT;' FROM @logins;   EXEC [master].sys.sp_executesql @cmd;   -- report SELECT [login], [db], [Access?] = CASE error 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), error) END FROM #x ORDER BY [login],db; GO   --DROP TABLE #x;

 

 

위 스크립트를 활용하여 특정 계정에 대해 데이터베이스 접근 권한을 확인하고 불필요한 계정의 접근을 미리 차단하여 보안에 주의 할 수 있도록 한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3399/verify-the-databases-a-sql-server-login-can-see--and-why/

 

 

2014-11-18 / 강성욱 / http://sqlmvp.kr

 

SQL Server 사용자 계정, SQL Server 계정, 사용자 보안, 로그인, SQL 로그인, 데이터베이스 접근, SQL Server, ms sql, create login, server role