데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

비관리자 계정으로 쿼리 계획 보기

 

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

 

SQ Server를 운영하거나 개발 할 때 어떤 권한의 계정으로 사용하는가? 아마 대부분의 관리자는 sysadmin의 강력한 권한을 가지고 있을 것이다. 그러나 호스팅을 하여 사용하는 경우 또는 보안상 권한이 매우 제한적인 사용자일 경우 여러 가지 제약을 받게 된다.

 

이번 포스트는 Sysadmin이 아니거나 View Server State 권한 없을 때 쿼리 계획을 가져오는 방법을 알아 본다. 이 방법은 트위터의 #sqlhelp 해시태그에 배치되어 있다.

 

SQL Server에서 권한이 없을 때 쿼리 계획을 확인하는 방법은 SQL Server에서 가장을 사용하는 것이다. 다음과 같은 시나리오를 생각해 볼 수 있다.

 

SQL Server에 [sentinel] 이라는 View Server State 권한이 부여된 계정이 있다. 또 다른 계정 [Agent]는 sysadmin이나 View Server State 권한을 가지고 있지 않다. [Agent] 로그인으로 현재 데이터베이스에서 실행되는 저장 프로시저 계획을 볼 수 있는 방법을 알아보자.

 

첫 번째 작업으로 [sentinel]의 View Server State 권한을 계정 [Agent]이게 권한을 가장 한다.

USE MASTER

GRANT VIEW SERVER STATE TO [sentinel];

GRANT IMPERSONATE ON LOGIN::[sentinel] to [agent];

GO

 

권한을 가장 하였으면 그 다음으로 [agent] 로그인이 현재 데이터베이스 컨텍스트에 사용할 수 있는 캐시된 계획을 볼 수 있도록 하는 저장 프로시저를 만든다.

CREATE PROCEDURE [dbo].[usp_SeePlan]

 

AS

 

SET NOCOUNT ON

 

EXECUTE AS LOGIN = 'sentinel';

SELECT 'Executed as user: ' + SUSER_NAME()

SELECT * FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp

    CROSS APPLY sys.dm_exec_sql_text (plan_handle) qt

WHERE qp.dbid = db_id();

 

/* Add your diagnostic query here The above query is an example */

 

REVERT;

SELECT 'Reverted back to user: ' + SUSER_NAME()

 

 

데이터베이스에 계정 [Agent]가 오류가 나타나면 해당 데이터 베이스에 [Agent] 사용자 생성을 하고 저장 프로시저에 대한 권한을 부여한다.

CREATE USER [agent] FOR LOGIN [agent] WITH DEFAULT_SCHEMA=[dbo];

GRANT EXECUTE ON OBJECT::[usp_SeePlan] TO agent;

 

[Agent] 계정으로 프로시저를 실행할 때 [sentinel] 계정 액세스 오류가 나타나면 다음 스크립트를 실행 한다.

CREATE USER [sentinel] FOR LOGIN [sentinel] WITH DEFAULT_SCHEMA=[dbo]

 

 

이제 [Agent] 계정으로 저장 프로시저를 실행해 보자. [Sentinel] 계정을 가장하여 프로시저를 실행하여 쿼리 계획을 확인 할 수 있다.

 

 

[참고자료]

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2306
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1560
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1743
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4606
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1744
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1562
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1540
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7854
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2214
» 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1243
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1772
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1782
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1473
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1862
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1651
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1392
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1161
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2534





XE Login