컴퓨터2007.11.15 14:42
exec sp_MSdbuseraccess  N'db', N'%'
 
이 프로시져는 사용자가 접근가능한 모든 데이터베이스 목록을 확인 할때 사용을 합니다.
별로 쓰일 용도가 없을것 같지만, MSSQL 관리도구중 사용이 간단한 엔터프라이즈 메니져(Enterprise Manager. 이하 EM)로
접속을 하면 모든 데이터베이스 목록을 불러오는 부분에서 사용이 됩니다.

 
Microsoft 기술문서에 적혀있는 부분을 인용하면 다음과 같습니다.
기술문서 : KB88969
CAUSE
SQL Server Enterprise Manager calls the sp_MSdbuseraccess stored procedure to determine the accessibility of each database. The sp_MSdbuseraccess stored procedure then runs against every database in SQL Server to determine the user’s permission level in that database. For x number of databases, the sp_MSdbuseraccess stored procedure will run x + 1 times. These multiple calls can take several minutes to finish. A busy system can make the calls take longer to finish.

요약하면, "SQL 서버의 EM 으로 접속을 힐때 각각의 데이터베이스에 접속이 가능한지를 확인하는 저장프로시져라서, 모든 데이터베이스에 대해서 실행이 되기때문에 데이터베이스의 총갯수 + 1번 실행이 되어 로드율이 높을경우, 결과를 확인하는것이 오래걸린다 " 라는 이야기 입니다.
 
많은 분들이 극찬을 하시는 MSSQL 도움말 을 보면 다음과 같이 나옵니다. 

MSSQL 도움말

sp_MShasdbaccess
사용자가 액세스할 수 있는 모든 데이터베이스의 이름과 소유자를 나열합니다.

구문
sp_MShasdbacess

반환 코드 값
0(성공) 또는 1(실패)

사용 권한
public 역활에 대한 기본 권한을 실행합니다.
 
빈번하게 사용되는 EM 이고, 응답시간이 느려서 EM 이 시간제한을 무제한으로 변경하여도 시간제한으로 끊어지는 현상 이 발생하는 힘든 상황에서도 이 프로시져를 수정하여서 응답속도를 줄일수만 있다면 상당한 효과를 볼수 있습니다. 사용량이 많은 SQL 서버의 경우, EM 으로의 작업이 어렵습니다. 그나마 로컬(터미널/콘솔)에서의 작업은 조금 느려도 할만하겠지만, 외부에서 EM 을 이용한 작업은 정말 눈물을 머금게합니다.
이러한 저와 같은 EM 에 최적화(?)되어 있는 사용자들에게는 반드시 필요한 부분이고, 저또한 사용자의 하나로써 적극 권장합니다.


 사용방법은 간단합니다.
위의 쿼리를 쿼리분석기로 실행만 하면 바로 적용이 됩니다.

1. EM 에 접속해서 현재의 상황을 확인합니다. 2. 위의 코드를 쿼리분석기에 입력합니다.
3. 쿼리를 실행합니다. 4. EM 으로 접속을 해봅니다.


위 테스트는 일반 사용자계정을 대상으로 진행을 하였습니다. 관리자(SystemAdmin)계정은 모든 데이터베이스에 접근권한이 있기때문에 적용을 하여도 차이가 없습니다.

출처 : MS 기술문서 KB889696
Posted by Zasfe

댓글을 달아 주세요

  1. 유용한정보 감사합니다. ^^

    2007.11.15 16:19 [ ADDR : EDIT/ DEL : REPLY ]
  2. 이용우

    유용한 정보 감사합니다~
    혹시, 저런 방식으로 보이지 않게 처리된 디비를 다시 보이도록 하는
    방법도 알 수 있을까요?

    2010.02.25 13:58 [ ADDR : EDIT/ DEL : REPLY ]

컴퓨터2007.10.11 12:00
데이터베이스 접속량이 많아지고 빠른 응답속도를 보이는 저장프로시져보다 직접 서버에서 쿼리를 보내고 응답을 받는횟수가 증가함에 따라 엔터프라이즈 메니져(Enterprise Manager. 이하 EM)로 접속하는 사용자로부터 응답속도가 느리고 때로는 접속은 가능하지만 데이터베이스 목록부분이 안보인다는 내용을 전달받았다.

가장 좋은 방법은 서버사양을 올리면 되는 것이지만, 돈이 든다는 가장 큰 단점이 있다.


그래서 EM의 접속시 가장 문제가 되는 데이터베이스 목록이 안보이는 현상을 중심으로 접속속도 증가를 위한 방법을 찾아보기로 하였다.

우선 프로필러로 EM 접속시 발생하는 쿼리를 캡춰하고, 하나하나 쿼리의 내용을 확인하였다.

상세한정보를 확인가능하지만 쿼리를 알고 있어야 사용이 가능한 쿼리분석기(query analyzer, 이하 QA) 를 이용해서 클라이언트측 응답속도의 기준이 되는 클라이언트통계를 바탕으로 쿼리 처리시간 을 체크하였다.

가장 많은 처리시간을 보이는 쿼리는 사용자가 접근가능한 모든 데이터베이스 목록을 가져오는 프로시져(sp_MShasdbaccess) 였다.
Microsoft 기술문서 KB88969 에 의하면 이 프로시져를 수정함으로써 많은 효과를 볼수 있다고 하며,
SQLER.PE.KR 에서 "데이터베이스의 권한이 있는 데이터베이스만 보기" 라는 이름으로 수정방법이 공개되어 있는 프로시져였다.

그래서 이 sp_MShasdbaccess 프로시져 를 수정하고 재차 테스트를 진행하였다.

하지만 의외로 접속 속도는 크게 줄지 않았다.

그러면서 알게된것이지만 EM은 화면에 표시하는게 많아서인지 일반 사용자에게는 쓸모없는 데이터베이스 복제 관련 정보도 가져오고 있는 부분이 상당수가 있었다.
물론 보안강화를 위해 일반사용자의 권한을 제한하고 있기때문에 해당쿼리의 응답은 항상 같은 코드(권한 없음에 해당하는 반환코드)를 응답하고 있었다.

단독으로 사용되는 서버이기때문에 복제부분의 응답코드를 하드코딩하기로 하였다.

결국 해당 부분을 수정하는것으로 실서비스에는 아무런 영향도 주지않았으면서 EM 의 접속속도가 1/3 로 줄어들었으며, 이후 EM 접속시 느리다다거나 목록부분이 안보인다거나 하는 문의가 줄어들었다.

역시 사람은 문제가 생겨야 생각을 하는것 같다.
Posted by Zasfe

댓글을 달아 주세요

  1. 창해

    필자가 왜 이런 글을 썼는지 이해가 안가는군요. 도대체 목적하는 바가 뭐죠?

    2007.11.02 19:58 [ ADDR : EDIT/ DEL : REPLY ]
    • 무엇인가 동기가 있거나 자기만의 그릇에 빠져서 정체되어 있는 저의 모습을 돌아보며, 조금더 공부하는 자세가 되자는 저의 생각이였습니다.
      관심을 가져주셔서 감사합니다.

      2007.11.14 09:09 [ ADDR : EDIT/ DEL ]

컴퓨터2007.06.29 17:11

엔터프라이즈메니져(EM)와 쿼리분석기는 모든 데이터베이스의 목록을 가져오는것을 기본으로 하기때문에 사용량이 많다거나 가져올 데이터가 많은경우 연결이 힘들고 연결되었다 하더라도 사용이 힘들게 됩니다.

하지만 엔터프라이즈메니져(EM)와 쿼리분석기는 특정 프로시져를 호출함으로써 데이터베이스목록을 가져오는 방식으로 동작을 하고 있습니다.
즉, 그 프로시져를 수정하면 가져오는 데이터베이스 리스트를 컨트롤할수있다는것을 알았습니다.( 감격..ㅠ_ㅠ)
하지만 문서화되어 있지 않아서 직접 분석을 할수밖에 없었습니다.

목표는 사용자로 하여금 접속권한이 있는 데이터베이스 리스트만을 보여주도록 프로시져를 변경하는것으로 정했으며, 프로시져의 분석(?) 을 하였더니 좀 허무하더군요..

원본은 sysdatabases 에서 select하는 부분이 전부 였습니다. 머야이거.;;
그래서 권한이 있는 데이터베이스 리스트만 보여지도록 임시테이블을 사용하여 프로시져를 수정 하였습니다.

DBA 가 보면 피식 거릴만한 정도로 단순한 수정입니다.(DBA는 정말 위대하다.. ㅠㅠ)

저장프로시져명 : sp_catalogs_rowset;2

원본
create procedure sp_catalogs_rowset;2
 (
 @dummy  int /* remove when Hydra bug 17032 is fixed */
 )
as
 select
  CATALOG_NAME = name,
  DESCRIPTION = convert(nvarchar(1),null)
 from  master.dbo.sysdatabases
 order by 1


수정본
ALTER procedure sp_catalogs_rowset;2
 (
 @dummy  int /* remove when Hydra bug 17032 is fixed */
 )
as
DECLARE @strSQL VARCHAR(1000)
DECLARE @exec_query VARCHAR(1000)
set nocount on
BEGIN TRAN
 BEGIN
 create table [#catalog_access]
 (
 [name] [sysname] NOT NULL,
 [DBaccess] [bit] NOT NULL
 )
 select @strSQL ='select name, DBaccess=has_dbaccess(name) from master.dbo.sysdatabases'
 set @exec_query = ' insert [#catalog_access] ' + @strSQL
 EXEC (@exec_query)
 select
  CATALOG_NAME = name,
  DESCRIPTION = convert(nvarchar(1),null)
 from #catalog_access
 where DBaccess=1
 order by 1
 Drop Table [#catalog_access]
 END
IF (@@ERROR <> 0)
 BEGIN
  ROLLBACK TRAN
  RETURN 0
 END
ELSE
 BEGIN
  COMMIT TRAN
  RETURN 1
 END

Posted by Zasfe

댓글을 달아 주세요