SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE LOGIN Rita WITH PASSWORD = 'MeterMaid'
go
USE Playground
go
CREATE USER Rita
ALTER ROLE Playrole ADD MEMBER Rita
go
-------------------------------------------------------------------------------------------------------
USE master
go
CREATE DATABASE DirectPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN DirectPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN DirectPlay$owner DISABLE
DENY CONNECT SQL TO DirectPlay$owner
ALTER AUTHORIZATION ON DATABASE::DirectPlay TO DirectPlay$owner
go
-------------------------------------------------------------------------------------------------------
USE DirectPlay
go
CREATE TABLE Directtable (id       int          NOT NULL,
                          somedata nvarchar(40) NOT NULL,
                          whodidit sysname      NOT NULL
                               CONSTRAINT Direct_default_whodidit DEFAULT USER,
                           CONSTRAINT pk_Direct PRIMARY KEY (id)
)
go
CREATE PROCEDURE add_directdata @id int, @somedata nvarchar(40) AS
   INSERT Directtable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM Directtable WHERE id = @id
go
EXEC add_directdata 1, 'The Long and Winding Road'
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
CREATE PROCEDURE get_direct_data @id int AS
   SELECT id, somedata, whodidit
   FROM   DirectPlay.dbo.Directtable
   WHERE  id = @id
go
EXECUTE AS LOGIN = 'Rita'
EXEC get_direct_data 1
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE DirectPlay
go
CREATE ROLE PlaygroundUsers
CREATE USER Rita
ALTER ROLE PlaygroundUsers ADD MEMBER Rita
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC get_direct_data 1
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE DirectPlay
go
CREATE SCHEMA PlaygroundGuests
go
GRANT EXECUTE ON SCHEMA::PlaygroundGuests TO PlaygroundUsers
go
CREATE PROCEDURE PlaygroundGuests.get_direct_data @id int AS
   SELECT id, somedata, whodidit
   FROM   dbo.Directtable
   WHERE  id = @id
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
ALTER PROCEDURE get_direct_data @id int AS
   EXEC DirectPlay.PlaygroundGuests.get_direct_data @id
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'Rita'
EXEC get_direct_data 1
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE master
go
CREATE DATABASE PlaySign
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlaySign$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlaySign$owner DISABLE
DENY CONNECT SQL TO PlaySign$owner
ALTER AUTHORIZATION ON DATABASE::PlaySign TO PlaySign$owner
go
USE PlaySign
go
CREATE TABLE Signtable (id       int          NOT NULL,
                        somedata nvarchar(40) NOT NULL,
                        whodidit sysname      NOT NULL
                             CONSTRAINT Sign_default_whodidit DEFAULT USER,
                        CONSTRAINT pk_Sign PRIMARY KEY (id)
)
go
CREATE PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
   INSERT Signtable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM Signtable WHERE id = @id
go
EXEC add_signdata 1, 'Back in the USSR'
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
CREATE PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
   DECLARE @low  int = 20000,
           @high int = 15000

   IF @id BETWEEN @high AND @low
   BEGIN
      INSERT PlaySign.dbo.Signtable(id, somedata)
         VALUES(@id, @somedata)

      SELECT id, somedata, whodidit
      FROM   PlaySign.dbo.Signtable
      WHERE  id = @id
   END
   ELSE
      RAISERROR('Illegal id. The permitted range is %d - %d', 16, 1, @low, @high)
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
CREATE CERTIFICATE [SIGN add_signdata for PlaySign]
ENCRYPTION BY PASSWORD = 'Lucy in the Sky with Diamonds'
WITH SUBJECT = '"GRANT INSERT, SELECT ON Signtable"'
go
ADD SIGNATURE TO dbo.add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign]
   WITH PASSWORD = 'Lucy in the Sky with Diamonds'
go
--ALTER CERTIFICATE [SIGN add_signdata for PlaySign] REMOVE PRIVATE KEY
go
DECLARE @cert_id int = cert_id('[SIGN add_signdata for PlaySign]')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id)

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE [SIGN Playground.dbo.add_signdata] 
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1)

PRINT @sql

USE PlaySign

EXEC (@sql)
go
CREATE USER [SIGN Playground.dbo.add_signdata] 
    FROM CERTIFICATE [SIGN Playground.dbo.add_signdata]
GRANT INSERT, SELECT ON dbo.Signtable TO [SIGN Playground.dbo.add_signdata]
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15001, 'You have a parking ticket!'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
ALTER PROCEDURE add_signdata @id int, @somedata nvarchar(40) AS
   DECLARE @low  int = 20000,
           @high int = 15000

   SELECT name, type, usage FROM sys.user_token
   SELECT name, type, usage FROM PlaySign.sys.user_token

   IF @id BETWEEN @high AND @low
   BEGIN
      INSERT PlaySign.dbo.Signtable(id, somedata)
         VALUES(@id, @somedata)

      SELECT id, somedata, whodidit
      FROM   PlaySign.dbo.Signtable
      WHERE  id = @id
   END
   ELSE
      RAISERROR('Illegal id. The permitted range is %d - %d', 16, 1, @low, @high)
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15002, 'No, Sir, you cannot park here'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
ADD SIGNATURE TO dbo.add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign]
   WITH PASSWORD = 'Lucy in the Sky with Diamonds'
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15002, 'No, Sir, you cannot park here'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE PlaySign
go
REVOKE CONNECT FROM [SIGN Playground.dbo.add_signdata]
go
USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15003, 'When I am free to take some tea with you???'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
DROP SIGNATURE FROM add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign]
DROP CERTIFICATE [SIGN add_signdata for PlaySign]
go
USE PlaySign
go
DROP USER [SIGN Playground.dbo.add_signdata] 
DROP CERTIFICATE [SIGN Playground.dbo.add_signdata]
go
-------------------------------------------------------------------------------------------------------
USE PlaySign
go
CREATE CERTIFICATE [SIGN Playground.dbo.add_signdata] 
ENCRYPTION BY PASSWORD = 'Lucy in the Sky with Diamonds'
WITH SUBJECT = '"GRANT INSERT, SELECT ON SignTable"'
go
CREATE USER [SIGN Playground.dbo.add_signdata] 
    FROM CERTIFICATE [SIGN Playground.dbo.add_signdata]
GRANT INSERT, SELECT ON dbo.Signtable TO [SIGN Playground.dbo.add_signdata]
go
-------------------------------------------------------------------------------------------------------
USE PlaySign
go
DECLARE @cert_id int = cert_id('[SIGN Playground.dbo.add_signdata]'),
        @public_key  varbinary(MAX),   
        @signature varbinary(8000),
        @certsql nvarchar(MAX),
        @signsql nvarchar(MAX)


SELECT @public_key = certencoded(@cert_id)

SELECT @certsql = 'CREATE CERTIFICATE [SIGN add_signdata for PlaySign] 
                   FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1)

SELECT @signature = signbycert (@cert_id, definition, 
                                N'Lucy in the Sky with Diamonds')
FROM   Playground.sys.objects o
JOIN   Playground.sys.sql_modules sm ON o.object_id = sm.object_id
WHERE  o.name = 'add_signdata'
  AND  o.schema_id = 1

SELECT @signsql = 'ADD SIGNATURE TO dbo.add_signdata 
                   BY CERTIFICATE [SIGN add_signdata for PlaySign]
                   WITH SIGNATURE = ' + convert(varchar(MAX), @signature, 1)

PRINT @certsql
PRINT @signsql

USE Playground

EXEC(@certsql)
EXEC(@signsql)
go
------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15004, 'Sit on the sofa with a sister or two'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
DROP SIGNATURE FROM add_signdata BY CERTIFICATE [SIGN add_signdata for PlaySign]
DROP CERTIFICATE [SIGN add_signdata for PlaySign]
go
USE PlaySign
go
DROP USER [SIGN Playground.dbo.add_signdata] 
DROP CERTIFICATE [SIGN Playground.dbo.add_signdata]
go
-------------------------------------------------------------------------------------------------------
/* Open GrantPermsToSP_server.sql and run it with this configuration:

-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260) = 'Playground',
        @procname nvarchar(520) = 'dbo.add_signdata'

-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('CONNECT SQL')
*/
go
-------------------------------------------------------------------------------------------------------
USE PlaySign
go
CREATE USER "SIGN [Playground].[dbo].[add_signdata]"
GRANT INSERT, SELECT ON dbo.Signtable TO "SIGN [Playground].[dbo].[add_signdata]"
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_signdata 15005, 'Me, looking older because I have a cap?'
go
REVERT
-------------------------------------------------------------------------------------------------------
USE master
go
CREATE DATABASE PlayChain1
CREATE DATABASE PlayChain2
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayChain$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayChain$owner DISABLE
DENY CONNECT SQL TO PlayChain$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain1 TO PlayChain$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain2 TO PlayChain$owner
go
-------------------------------------------------------------------------------------------------------
USE PlayChain1
go
CREATE USER Rita
go
CREATE TABLE Chaintable (id       int          NOT NULL,
                         somedata nvarchar(40) NOT NULL,
                         whodidit sysname      NOT NULL
                              CONSTRAINT Chain_default_whodidit DEFAULT USER,
                         CONSTRAINT pk_Chain PRIMARY KEY (id)
)
go
USE PlayChain2
go
CREATE USER Rita
CREATE ROLE ChainUsers
ALTER ROLE ChainUsers ADD MEMBER Rita
GRANT EXECUTE ON SCHEMA::dbo TO ChainUsers
go
CREATE PROCEDURE add_chaindata @id int, @somedata nvarchar(40) AS
   INSERT PlayChain1.dbo.Chaintable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM PlayChain1.dbo.Chaintable WHERE id = @id
go
-------------------------------------------------------------------------------------------------------
USE PlayChain2
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_chaindata 1, 'Parking in the middle of a roundabout?'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
ALTER DATABASE PlayChain1 SET DB_CHAINING ON
ALTER DATABASE PlayChain2 SET DB_CHAINING ON
go
-------------------------------------------------------------------------------------------------------
USE PlayChain2
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_chaindata 1, 'Parking in the middle of a roundabout?'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE PlayChain1
go
CREATE USER SgtPepper
go
USE PlayChain2
go
CREATE USER SgtPepper
ALTER ROLE ChainUsers ADD MEMBER SgtPepper
go
-------------------------------------------------------------------------------------------------------
ALTER DATABASE Playground SET DB_CHAINING ON
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
CREATE PROCEDURE my_add_chaindata @id int, @somedata nvarchar(40) AS
   INSERT PlayChain1.dbo.Chaintable(id, somedata, whodidit)
      VALUES(@id, @somedata, 'Kilroy was here!')
   SELECT id, somedata, whodidit FROM PlayChain1.dbo.Chaintable WHERE id = @id
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
EXEC my_add_chaindata 2, 'With Love from Me to You'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER PlayChain$owner
ALTER AUTHORIZATION ON my_add_chaindata TO PlayChain$owner
EXEC my_add_chaindata 2, 'With Love from Me to You'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE Playground
go
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE PROCEDURE list_jobs AS
    SELECT * FROM msdb.dbo.sysjobs
go
EXEC list_jobs
go
REVERT
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER sa
go
REVERT
go
--------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER my_sa FOR LOGIN sa
go
REVERT
go
---------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
go
CREATE USER my_sa FOR LOGIN DirectPlay$owner 
ALTER USER my_sa WITH LOGIN = sa
go
REVERT
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'SgtPepper'
go
ALTER AUTHORIZATION ON list_jobs TO my_sa
EXEC list_jobs
go
REVERT
-------------------------------------------------------------------------------------------------------
USE master
go
SELECT *
FROM   sys.all_objects o
WHERE  NOT EXISTS (SELECT *
                   FROM   sys.database_permissions dp
                   WHERE  dp.major_id = o.object_id
                     AND  dp.class = 1
                     AND  dp.grantee_principal_id = 0)
--------------------------------------------------------------------------------------------------------
USE master
go
CREATE DATABASE ImpersonalPlay1
CREATE DATABASE ImpersonalPlay2 WITH TRUSTWORTHY ON
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN ImpersonalPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN ImpersonalPlay$owner DISABLE
DENY CONNECT SQL TO ImpersonalPlay$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain1 TO ImpersonalPlay$owner
ALTER AUTHORIZATION ON DATABASE::PlayChain2 TO ImpersonalPlay$owner
go
-------------------------------------------------------------------------------------------------------
USE ImpersonalPlay1
go
CREATE TABLE ImpersonalTable
             (id       int          NOT NULL,
              somedata nvarchar(40) NOT NULL,
              whodidit sysname      NOT NULL
                 CONSTRAINT Impers_default_whodidit DEFAULT original_login(),
              CONSTRAINT pk_impersonal PRIMARY KEY (id)
)
go
CREATE USER ImpersonalReader WITHOUT LOGIN
CREATE USER ImpersonalWriter WITHOUT LOGIN
ALTER ROLE db_datareader ADD MEMBER ImpersonalReader
ALTER ROLE db_datareader ADD MEMBER ImpersonalWriter
ALTER ROLE db_datawriter ADD MEMBER ImpersonalWriter
go
-------------------------------------------------------------------------------------------------------
USE ImpersonalPlay2
go
DECLARE @sql nvarchar(MAX)
SELECT @sql = (SELECT 'CREATE USER ' + quotename(name) + ' WITHOUT LOGIN ' +
                      'WITH SID = ' + convert(varchar(200), sid, 1) +
                      char(13) + char(10)
               FROM   ImpersonalPlay1.sys.database_principals
               WHERE  name LIKE 'Impersonal%'
                 AND  type = 'S'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
PRINT @sql
EXEC(@sql)
go
-------------------------------------------------------------------------------------------------------
CREATE USER Rita
CREATE ROLE ImpersonalUsers
ALTER ROLE ImpersonalUsers ADD MEMBER Rita
GRANT EXECUTE ON SCHEMA::dbo TO ImpersonalUsers
go
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE add_impersonaldata @id int, @somedata nvarchar(40)
   WITH EXECUTE AS 'ImpersonalWriter' AS
   INSERT ImpersonalPlay1.dbo.ImpersonalTable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit
   FROM   ImpersonalPlay1.dbo.ImpersonalTable
   WHERE id = @id
go
-------------------------------------------------------------------------------------------------------
USE ImpersonalPlay2
go
EXECUTE AS LOGIN = 'Rita'
EXEC add_impersonaldata 2, 'We need to tow this car away'
go
REVERT
go