sysadmin 이 아닌 사용자로 실행하는 dynamic sql 에서 querytraceon 힌트 사용하기

CREATE LOGIN SQLTAG_GOD WITH PASSWORD = 'P@ssw0rd' -- 로그인생성
GO
USE MASTER
GO
CREATE USER SQLTAG_GOD FOR LOGIN SQLTAG_GOD  -- User 생성
GO
ALTER SERVER ROLE SYSADMIN ADD MEMBER SQLTAG_GOD
GO

USE [master]
GO
DENY CONNECT SQL TO SQLTAG_GOD
GO
ALTER LOGIN SQLTAG_GOD DISABLE
GO

USE [DBA]
GO
CREATE USER SQLTAG_GOD FOR LOGIN SQLTAG_GOD  -- User 생성
GO
-- ALTER ROLE DB_OWNER ADD MEMBER SQLTAG_GOD
GO

use dba
go
if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int)
go

insert into tblx values (1)
go

if object_id('usp_dynamic_querytraceon') is null
exec ('create proc usp_dynamic_querytraceon as select 1 ')
go

use dba
go

alter proc usp_dynamic_querytraceon
--with execute as owner -- 정적 sql 일때는 execute as owner 만 있으면 된다.
as
execute as login ='sqltag_god'
exec ('SELECT count(*) cnt FROM dbo.tblx  OPTION (QUERYTRACEON 8649)')
-- adhoc 일때는 planguide 로 먹는다.
-- dynamic sql 일때는 execute as login 으로 IMPERSONATE 권한을 가진 사용자가 실행하면 된다.
go

CREATE LOGIN sida WITH PASSWORD = '1234' -- 로그인생성
GO
USE MASTER
GO
CREATE USER sida FOR LOGIN sida  -- User 생성
GO
USE dba
GO
CREATE USER sida FOR LOGIN sida  -- User 생성
GO
GRANT EXECUTE TO sida
go

-- sida 는 [SQLTAG_GOD] 으로 변신할수 있는 힘을주겠다.........
use [master]
GO
GRANT IMPERSONATE ON LOGIN::[SQLTAG_GOD] TO sida -- WITH GRANT OPTION
GO
-- end

-- 다른 session 에서 sida 로 로그인 해 아래 스크립트를 실행해 본다.
use dba
go
exec usp_dynamic_querytraceon
go

 

-- IMPERSONATE  권한이 있으면 다음과 같은 쿼리도 가능하다.

EXECUTE ('SELECT * FROM dbo.tblx  OPTION (QUERYTRACEON 8649)') AS LOGIN='sqltag_god'

 

sa 이름이 바뀌지 않았다면  sa 로 해도 됩니다.