Here is a complete and tested example with signed procedure:
-- Script by Vedran Kesegic, 20.3.2013.
-- We will create a database and a procedure which calls sp_who2.
-- We will also create a low-privileged login and give him exec permission on that procedure.
create database TestCert
create login LowPriv with password='Str0ngPWD!'
GO
use TestCert
create user LowPriv for login LowPriv
GO
create procedure dbo.show_processes
--with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server level
as
begin
select * from sys.login_token -- who am i
select * from sys.fn_my_permissions(null,'server') -- what server rights do i have
exec sys.sp_who2
end
GO
grant exec on dbo.show_processes to LowPriv
GO
execute as login='LowPriv'
exec sys.sp_who2 -- shows only my session, not good
exec dbo.show_processes -- shows only my session, not good
revert
GO
-- Create login to which we will grant view server state. Login will be mapped to certificate.
-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.
-- certificate must be in master database in order to be mapped to login
use master -- create certificate in master database
create certificate HighPrivCert
ENCRYPTION BY PASSWORD = 'Str0ngPWD!'
WITH SUBJECT = 'Certificate for signing stored procedures'
select * from sys.certificates
GO
create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login
grant view server state to HighPrivCertLogin
GO
-- in order to grant db user rights, we must transfer to that db the same certificate
backup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(FILE = 'C:\temp\HighPrivCert.PVK',
DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key
ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file
)
GO
use TestCert
create certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(FILE = 'C:\temp\HighPrivCert.PVK',
DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file
ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key
)
select * from sys.certificates
-- Manually delete cert files NOW!
GO
-- by signing procedure, it will run under login associated with that certificate
ADD SIGNATURE TO OBJECT::dbo.show_processes
BY CERTIFICATE HighPrivCert
WITH PASSWORD='Str0ngPWD!'
GO
execute as login='LowPriv'
exec sys.sp_who2 -- shows only my session
exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.
revert
GO
-- CLEANUP
/*
use master
drop database TestCert
drop login HighPrivCertLogin
drop certificate HighPrivCert
drop login LowPriv
*/
Enjoy!