SET XACT_ABORT, NOCOUNT ON
USE master
go
CREATE SERVER ROLE PowerUsers
CREATE LOGIN SgtPepper WITH PASSWORD = 'Lonely Hearts Club Band'
ALTER SERVER ROLE PowerUsers ADD MEMBER SgtPepper
go
CREATE VIEW logintokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.login_token
go
USE Playground
go
CREATE USER SgtPepper
ALTER ROLE db_owner ADD MEMBER SgtPepper
go
CREATE VIEW logintokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.login_token
go
------------------------------------------------------------------------------------------------------
USE master
go
CREATE PROCEDURE sp_ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
------------------------------------------------------------------------------------------------------
GRANT EXECUTE ON sp_ShowSessions TO public
go
------------------------------------------------------------------------------------------------------
USE Playground
go
EXEC sp_ShowSessions
EXECUTE AS LOGIN = 'SgtPepper'
EXEC sp_ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
USE master
go
CREATE CERTIFICATE spShowSessions$cert
ENCRYPTION BY PASSWORD = 'Magical Mystery Tour'
WITH SUBJECT = '"GRANT VIEW SERVER STATE"'
go
ADD SIGNATURE TO sp_ShowSessions BY CERTIFICATE spShowSessions$cert
    WITH PASSWORD = 'Magical Mystery Tour'
go
CREATE LOGIN spShowSessions$certlogin FROM CERTIFICATE spShowSessions$cert
SELECT * FROM sys.server_principals WHERE name = 'spShowSessions$certlogin'
go
GRANT VIEW SERVER STATE TO spShowSessions$certlogin
go
------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC sp_ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
USE Playground
go
CREATE PROCEDURE Management.ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
------------------------------------------------------------------------------------------------------
USE Playground 
go
CREATE CERTIFICATE PlaygroundShowSessions$cert
ENCRYPTION BY PASSWORD = 'And Your Bird Can Sing'
WITH SUBJECT = '"GRANT VIEW SERVER STATE"'

ADD SIGNATURE TO Management.ShowSessions BY CERTIFICATE PlaygroundShowSessions$cert
   WITH PASSWORD = 'And Your Bird Can Sing'
go
------------------------------------------------------------------------------------------------------
ALTER CERTIFICATE PlaygroundShowSessions$cert REMOVE PRIVATE KEY
go
------------------------------------------------------------------------------------------------------
USE Playground
go
DECLARE @public_key varbinary(MAX) = 
           certencoded(cert_id('PlaygroundShowSessions$cert'))

USE master

CREATE CERTIFICATE PlaygroundShowSessions$cert 
FROM BINARY = @public_key
go
------------------------------------------------------------------------------------------------------
USE Playground
go
DECLARE @public_key varbinary(MAX) = 
            certencoded(cert_id('PlaygroundShowSessions$cert')),
        @sql nvarchar(MAX)

SELECT @sql =
   'CREATE CERTIFICATE PlaygroundShowSessions$cert
    FROM BINARY = ' + convert(varchar(MAX), @public_key, 1)

USE master

PRINT @sql
EXEC(@sql)
go
------------------------------------------------------------------------------------------------------
CREATE LOGIN PlaygroundShowSessions$certlogin 
    FROM CERTIFICATE PlaygroundShowSessions$cert

GRANT VIEW SERVER STATE TO PlaygroundShowSessions$certlogin
go
------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
USE master
go
DROP LOGIN PlaygroundShowSessions$certlogin
DROP CERTIFICATE PlaygroundShowSessions$cert
go
------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
USE Playground
go
BACKUP CERTIFICATE PlaygroundShowSessions$cert TO FILE = 'C:\temp\certexport.cer'
go
USE master
go
CREATE CERTIFICATE PlaygroundShowSessions$cert FROM FILE = 'C:\temp\certexport.cer'
EXEC xp_cmdshell 'DEL C:\temp\certexport.cer'
go
------------------------------------------------------------------------------------------------------
CREATE LOGIN PlaygroundShowSessions$certlogin 
    FROM CERTIFICATE PlaygroundShowSessions$cert

GRANT VIEW SERVER STATE TO PlaygroundShowSessions$certlogin
go
------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
USE Playground
go
ALTER PROCEDURE Management.ShowSessions AS
   SELECT * FROM logintokeninfo

   IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
       has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
      SELECT * FROM sys.dm_exec_sessions WHERE database_id = db_id()
   ELSE
      RAISERROR('You don''t have permission to run this procedure!', 16, 1)
go
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
-- First run GrantPermsToSP_server.sql.
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
go