[MSSQL] DTS 와 쿼리분석기에서 사용자DB만 보이기

엔터프라이즈메니져(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