[SQL SERVER]讓一般使用者執行DBCC TraceON
一般給予AP的資料庫帳號基本是以最小為原則，
日前進行效能調校時，我為了提高FullScan速度不得已使用querytraceon 8649，
但DBCC Traceon需要sysadmin role才能順利執行，我雖然嘗試使用execute as 就是行不通，
後來G到一篇透過簽章方式假冒具有sysadmin role使用者來執行，
自己寫下來備忘。
--querytraceon 8649 test
create proc dbo.Test
AS
set nocount on
select * from AdventureWorks2012.dbo.SalesOrderHeader t1
join AdventureWorks2012.dbo.SalesOrderDetail t2 on t1.SalesOrderID=t2.SalesOrderID
option(recompile,querytraceon 8649)
go
--建立資料庫主要金鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxx';
go
use mydb
go
CREATE CERTIFICATE mycert
WITH SUBJECT = 'Mycertificate';
--使用憑證來簽署SP
ADD SIGNATURE TO dbo.Test
BY CERTIFICATE mycert;
GO
--移除私鑰
ALTER CERTIFICATE mycert
REMOVE PRIVATE KEY;
GO
--取得該憑證公開部分的二進位格式
SELECT CERTENCODED(CERT_ID('mycert'));
GO
USE master;
GO
--將憑證加入至 master 資料庫中
CREATE CERTIFICATE mycert
FROM BINARY =0x……
go
--建立登入並使用該憑證
CREATE LOGIN mycertuser
FROM CERTIFICATE mycert;
GO
--移除連接權限
REVOKE CONNECT SQL FROM mycertuser;
GO
--將該使用者加入 sysadmin role
ALTER SERVER ROLE sysadmin
ADD MEMBER mycertuser;
--建立一般登入使用者
CREATE LOGIN myTest
WITH PASSWORD = 'xxxx', DEFAULT_DATABASE = mydb;
use mydb
go
--建立資料庫使用者
CREATE USER myTest FROM LOGIN myTest;
--授予執行TEST權限
GRANT EXECUTE ON OBJECT::Test TO myTest;
使用mytest 使用者執行該SP
強制平行執行計畫
drop user myTest
drop login myTest
use master
go
drop LOGIN mycertuser
drop CERTIFICATE mycert
--Update For SQL2012以前版本
由於 CERTENCODED function是SQL2012以後才有的，
所以之前的版本要把憑證匯入到其他資料庫需使用以下方法
1.備份現有憑證
BACKUP CERTIFICATE mycert TO FILE = 'e:\mycert';
2.從檔案匯入憑證
CREATE CERTIFICATE mycert
FROM FILE = 'e:\mycert'
參考
Next-Level Parallel Plan Forcing: An Alternative to 8649