SQL Stored Procedure mit Zertifikat

Benötigt ein Benutzer Rechte auf System Views oder System Tabellen kann man ihm den Zugriff darauf über eine Stored Procedure gewähren.
Damit hat der Benutzer nur das spezifische Recht, das in der Stored Procedure verwendet wird und benötigt keine SA oder "VIEW SERVER STATE" Rechte Die Stored Procedure bekommt ein CERTIFICATE LOGIN und dieser Login bekommt das SA oder VIEW SERVER STATE Recht Der Benutzer bekommt nur das Recht, die Stored Procedure auszuführen und diese wird dann als der CERTIFICATE LOGIN ausgeführt

Um das einzurichten, wechselt man in die master Datenbank:

USE master
GO

Erstellt die Stored Procedure - in diesem Fall wird eine System View verwendet, die nur mit VIEW SERVER STATE Rechten funktioniert

CREATE PROC [sp_dm_tran_session_transactions] AS
BEGIN
   SELECT COUNT(*) 
     FROM SYS.DM_TRAN_SESSION_TRANSACTIONS
     WHERE IS_USER_TRANSACTION = 1
END;
GO

Erstellt das Zertifikat und sichert es mit einem Passwort

CREATE CERTIFICATE crt_dm_tran_session_transactions
    ENCRYPTION BY PASSWORD= '<PASSWORT>'
    WITH SUBJECT ='Certificate zum Abrufen von SYS.DM_TRAN_SESSION_TRANSACTIONS',
    START_DATE = '20181104',EXPIRY_DATE = '20501105'

Jetzt wird der LOGIN mit dem Zertifikat erstellt. Es wird kein User in der master Datenbank erstellt, sondern nur der LOGIN

CREATE LOGIN user_dm_tran_session_transactions FROM CERTIFICATE crt_dm_tran_session_transactions

Nun bekommt der CERTIFICATE LOGIN das Recht für VIEW SERVER STATE

GRANT VIEW SERVER STATE TO user_dm_tran_session_transactions

Die Signatur wird der Stored Proecdure zugewiesen

ADD SIGNATURE TO [sp_dm_tran_session_transactions] BY CERTIFICATE crt_dm_tran_session_transactions
       WITH PASSWORD ='<PASSWORT>'

Als letztes gibt man dem Benutzer das EXECUTE Rechte für die Stored Procedure

GRANT EXECUTE ON sp_dm_tran_session_transactions TO meinUser