SET XACT_ABORT, NOCOUNT ON
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN [PROXY Playground.Management.ShowSessions2] ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
go
ALTER LOGIN [PROXY Playground.Management.ShowSessions2] DISABLE
DENY CONNECT SQL TO [PROXY Playground.Management.ShowSessions2]
GRANT VIEW SERVER STATE TO [PROXY Playground.Management.ShowSessions2]
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
CREATE PROCEDURE Management.ShowSessions2
   WITH EXECUTE AS 'PROXY Playground.Management.ShowSessions2' 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.ShowSessions2
go
REVERT
go
-------------------------------------------------------------------------------------------------------
CREATE USER [PROXY Playground.Management.ShowSessions2]
go
-------------------------------------------------------------------------------------------------------
GRANT CONNECT TO [PROXY Playground.Management.ShowSessions2]
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT
go
-------------------------------------------------------------------------------------------------------
ALTER PROCEDURE Management.ShowSessions2
   WITH EXECUTE AS 'PROXY Playground.Management.ShowSessions2' AS
   SELECT * FROM logintokeninfo

   DECLARE @has_perm bit = 0
   EXECUTE AS CALLER
      IF (is_srvrolemember('PowerUsers') = 1 AND is_member('db_owner') = 1) OR
          has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 1
          SELECT @has_perm = 1
   REVERT

   IF @has_perm = 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.ShowSessions2
go
REVERT
go
-------------------------------------------------------------------------------------------------------
EXEC Management.ShowSessions2
go
-------------------------------------------------------------------------------------------------------
ALTER DATABASE Playground SET TRUSTWORTHY ON
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
EXEC Management.ShowSessions2
go
REVERT
go
--=======================================================================================================
-- Change connection to 'SgtPepper', password = Lonely Hearts Club Band.
USE Playground
go
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
-- DBCC TRACEON(4199)
REVERT
go
--=======================================================================================================
-- Change connection back to your own login.
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN Playground$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN Playground$owner DISABLE
DENY CONNECT SQL TO Playground$owner
ALTER AUTHORIZATION ON DATABASE::Playground TO Playground$owner
go
--=======================================================================================================
-- Change connection to SqlPepper again.
USE Playground
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT
go
-------------------------------------------------------------------------------------------------------
EXEC Management.ShowSessions2
go
--=======================================================================================================
-- Change connection back to your own login again.
USE master
GRANT AUTHENTICATE SERVER TO Playground$owner
go
--=======================================================================================================
-- Once more, change the connection to SgtPepper.
USE Playground
EXEC Management.ShowSessions2
go
-------------------------------------------------------------------------------------------------------
USE Playground
EXECUTE AS USER = 'dbo'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT
go
-------------------------------------------------------------------------------------------------------
CREATE USER [LIVERPOOL\GMartin]
EXECUTE AS USER = 'LIVERPOOL\GMartin'
SELECT is_srvrolemember('sysadmin') AS Sysadmin, * FROM logintokeninfo
go
REVERT
go
--=======================================================================================================
-- Change connection back to your own login.
USE Playground
go
EXECUTE AS USER = 'Michelle'
go
CREATE PROCEDURE #elevate AS
    EXECUTE AS USER = 'LIVERPOOL\GMartin'
    CREATE LOGIN Maxwell WITH PASSWORD = 'SilverHammer'
    ALTER SERVER ROLE sysadmin ADD MEMBER Maxwell
    REVERT
go
EXEC dynamic_playdata2 'zz'' EXEC #elevate --'
go
DROP PROCEDURE #elevate
go
REVERT
DROP LOGIN Maxwell
-----------------------------------------------------------------------------------------------------------
go
ALTER DATABASE Playground SET TRUSTWORTHY OFF