USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================
-- Author:	
-- Created: 03/14/13
-- Desc:	 sp_who for users
-- =============================
CREATE PROCEDURE [usp_who] @who INT = @@spid

with execute as self
AS

BEGIN
SET NOCOUNT ON;
exec sp_who @who;
END

-- 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
*/