SET NOCOUNT, XACT_ABORT ON
USE Playground
go
------------------------------------------------------------------------------------------------------
CREATE CERTIFICATE my_first_cert
ENCRYPTION BY PASSWORD = 'All You Need Is Love'
WITH SUBJECT = '"This is my first certificate"'
go
------------------------------------------------------------------------------------------------------
CREATE CERTIFICATE mysecondcert 
ENCRYPTION BY PASSWORD = 'Eight Days a Week'
WITH SUBJECT = 'Music, Song and Dance'
go
------------------------------------------------------------------------------------------------------
ADD SIGNATURE TO check_playdata
   BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love'
go
------------------------------------------------------------------------------------------------------
CREATE USER my_first_cert_user FROM CERTIFICATE my_first_cert
go
------------------------------------------------------------------------------------------------------
SELECT * FROM sys.database_principals WHERE name = 'my_first_cert_user'
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'my_first_cert_user'
go
------------------------------------------------------------------------------------------------------
CREATE USER my_second_cert_user FROM CERTIFICATE my_first_cert
go
------------------------------------------------------------------------------------------------------
SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
       c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT   JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id
go
------------------------------------------------------------------------------------------------------
GRANT VIEW DEFINITION ON Playtable TO my_first_cert_user
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT
go
------------------------------------------------------------------------------------------------------
CREATE VIEW tokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.user_token
go
------------------------------------------------------------------------------------------------------
SELECT * FROM tokeninfo
go
------------------------------------------------------------------------------------------------------
ALTER PROCEDURE check_playdata @id int AS
   SELECT * FROM tokeninfo
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT
go
------------------------------------------------------------------------------------------------------
ADD SIGNATURE TO check_playdata
   BY CERTIFICATE my_first_cert WITH PASSWORD = 'All You Need Is Love'
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata 1
go
REVERT
go
------------------------------------------------------------------------------------------------------
CREATE CERTIFICATE dynamic_playdata$cert
ENCRYPTION BY PASSWORD = 'While My Guitar Gently Weeps'
WITH SUBJECT = '"GRANT SELECT ON dbo.Playtable"'

ADD SIGNATURE TO dynamic_playdata BY CERTIFICATE dynamic_playdata$cert
   WITH PASSWORD = 'While My Guitar Gently Weeps'

CREATE USER dynamic_playdata$certuser FROM CERTIFICATE dynamic_playdata$cert

GRANT SELECT ON dbo.Playtable TO dynamic_playdata$certuser
go
------------------------------------------------------------------------------------------------------
SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
       c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT   JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata 'ensamble'
go
REVERT
go
------------------------------------------------------------------------------------------------------
CREATE PROCEDURE show_playindexes AS
   EXEC sp_helpindex 'Playtable'
go
EXECUTE AS USER = 'Michelle'
EXEC show_playindexes
go
REVERT
go
------------------------------------------------------------------------------------------------------
ADD SIGNATURE TO show_playindexes BY CERTIFICATE my_first_cert
   WITH PASSWORD = 'All You Need Is Love'
go
EXECUTE AS USER = 'Michelle'
EXEC show_playindexes
go
REVERT
go
------------------------------------------------------------------------------------------------------
CREATE PROCEDURE inner_sp AS
   SELECT 'inner_sp' AS wherearewe, *
   FROM   tokeninfo
go
CREATE TRIGGER play_tri ON Playtable AFTER INSERT AS
   SELECT 'In trigger' AS wherearewe, *
   FROM   tokeninfo
go
------------------------------------------------------------------------------------------------------
CREATE PROCEDURE outer_sp @id int, @somedata nvarchar(40) AS
   SELECT 'outer_sp' AS wherearewe, token_name, type, usage
   FROM   tokeninfo
   INSERT Playtable(id, somedata)
      VALUES(@id, @somedata)
   EXEC inner_sp
go
------------------------------------------------------------------------------------------------------
ADD SIGNATURE TO outer_sp BY CERTIFICATE my_first_cert
   WITH PASSWORD = 'All You Need Is Love'
go
EXECUTE AS USER = 'Michelle'
EXEC outer_sp 4, 'Testing the scope'
go
REVERT
go
------------------------------------------------------------------------------------------------------
DROP TRIGGER play_tri
go
------------------------------------------------------------------------------------------------------
DENY SELECT, UPDATE, DELETE, INSERT ON Playtable TO Michelle
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
go
EXEC add_playdata 5, 'Crawled off to sleep in the bath'
EXEC dynamic_playdata 'bath'
go
REVERT
go
------------------------------------------------------------------------------------------------------
REVOKE SELECT, UPDATE, DELETE, INSERT ON Playtable FROM Michelle
go
------------------------------------------------------------------------------------------------------
-- At this point, run GrantPermsToSP.sql!
DECLARE @perms Management.Permission_list
INSERT @perms (perm) VALUES('ALTER ON Playtable')
EXEC Management.GrantPermsToSP 'truncate_playdata', @perms, @debug = 1
go
------------------------------------------------------------------------------------------------------
DECLARE @perms Management.Permission_list
INSERT @perms (perm) VALUES('ALTER ON Playtable')
EXEC Management.GrantPermsToSP 'truncate_playdata', @perms, @debug = 1
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC truncate_playdata
go
REVERT
SELECT * FROM Playtable
go
------------------------------------------------------------------------------------------------------
EXEC Management.GrantPermsToSP 'truncate_playdata', @debug = 1
go
-------------------------------------------------------------------------------------------------------
DECLARE @perms Management.Permission_list
INSERT @perms (perm)
   VALUES('INSERT, SELECT, DELETE, UPDATE ON Playtable'),
         ('CREATE PROCEDURE'),
         ('IMPERSONATE ON USER::Michelle')
EXEC Management.GrantPermsToSP 'dbo.show_playindexes', @perms, @debug = 1
go
--------------------------------------------------------------------------------------------------------
SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
       c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT   JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id
go