SET XACT_ABORT, NOCOUNT ON
USE Playground
go
CREATE APPLICATION ROLE AppRole WITH PASSWORD = 'Tomorrow Never Knows'
GRANT EXECUTE, SELECT TO AppRole
go
EXECUTE AS LOGIN = 'SgtPepper'
CREATE TABLE #cookie(cookie varbinary(8000) NOT NULL)
DECLARE @cookie varbinary(8000)
EXEC sp_setapprole 'AppRole', N'Tomorrow Never Knows', 
                   @fCreateCookie = 'true', @cookie = @cookie OUTPUT
INSERT #cookie (cookie) VALUES (@cookie)
go
SELECT * FROM tokeninfo
SELECT * FROM logintokeninfo
EXEC Management.ShowSessions
INSERT Playtable(id, somedata) VALUES (-1, 'It was twenty years ago today')
go
DECLARE @cookie varbinary(8000)
SELECT @cookie = cookie FROM #cookie
EXEC sp_unsetapprole @cookie
DROP TABLE #cookie
go
REVERT
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'SgtPepper'
EXEC Management.ShowSessions
go
REVERT
go
------------------------------------------------------------------------------------------------------
CREATE TABLE audited
   (somedata int NOT NULL,
    moduser  sysname NOT NULL
       CONSTRAINT def_moduser DEFAULT
          coalesce(convert(nvarchar(128), session_context(N'Username')),
                   original_login())
)
go
-------------------------------------------------------------------------------------------------------
INSERT audited(somedata) VALUES (9)
EXEC sp_set_session_context 'Username', 'PolythenePam', @read_only = 1
INSERT audited(somedata) VALUES (64)
SELECT somedata, moduser FROM audited
go
-------------------------------------------------------------------------------------------------------
CREATE TABLE also_audited
   (somedata int NOT NULL,
    moduser  sysname NOT NULL
       CONSTRAINT also_def_moduser DEFAULT
          coalesce(convert(nvarchar(64),
                      substring(context_info(), 1, 
                                charindex(0x0000, context_info()) - 1)),
                   original_login())
)
go
---------------------------------------------------------------------------------------------------------
SET CONTEXT_INFO 0x
INSERT also_audited(somedata) VALUES (9)
DECLARE @contextinfo varbinary(128)
SELECT @contextinfo = convert(varbinary(128), N'RockyRacoon')
SET CONTEXT_INFO @contextinfo
INSERT also_audited(somedata) VALUES (64)
SELECT somedata, moduser FROM also_audited