SSMS에서 사용자 데이터베이스 목록이 보이지 않음

SQL 사용자 계정으로 데이터베이스에 로그온 했을 때, 전체 사용자 데이터베이스가 보이지 않고 자신이 소유자로 등록된 사용자 데비터베이스만 확인됩니다. 어떤 설정 때문에 전체 데이터베이스 목록이 보이지 않는 것일까요?


[환경]
SQL Server 2008 SP1


[현상]
SQL 사용자 laigo 계정으로 데이터베이스에 로그온 하였는데 자신이 master, tempdb 와 자신이 소유한 데이터베이스 목록만 확인이 됩니다. 모든 데이터베이스 목록이 확인되지 않는 원인 확인이 필요합니다.

1.  SSMS

2. 데이터베이스 조회

SELECT NAME FROM SYS.SYSDATABASES
/*
name
--------
master
tempdb
MESDATA
*/

[원인]
기본적으로 모든 데이터베이스 사용자는 Public Role에 포함되어 있으며 VIEW ANY DATABASE 권한이 부여되어 있습니다만 현재의 환경에서는 Public Role의 VIEW ANY DATABASE 서버 사용 권한이 REVOKE 되어 있습니다.


[해결방법]
1. 아래 쿼리를 사용하여 Public Role 에 VIEW ANY DATABASE 사용 권한을 부여합니다.

GRANT VIEW ANY DATABASE TO PUBLIC

2. 특정 로그인 계정에 VIEW ANY DATABASE 사용 권한을 부여하기 위해서 아래 쿼리를 사용할 수 있습니다.

GRANT VIEW ANY DATABASE TO <login>;

3. Pulbic Role 에 VIEW ANY DATABASE 사용 권한을 제거하는 방법은 아래와 같습니다.

REVOKE VIEW ANY DATABASE FROM PUBLIC

[분석결과]
VIEW ANY DATABASE 권한을 가진 보안 개체를 확인하였습니다만 'Public' 개체가 등록되어 있지 않습니다.

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON   p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;
GO
/*
결과 없음
*/

기본값은 아래와 같습니다.
/*
grantee_name      state_desc       permission_name
----------------------------------------------------
public            GRANT            VIEW ANY DATABASE
*/

[참고자료]
REVOKE Server Permissions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186308.aspx

VIEW ANY DATABASE Permission
http://msdn.microsoft.com/en-us/library/ms189077.aspx


작성자 : Lai Go / 작성일자 : 2010.03.02