--==========================================================================================
-- ON REMOTE SERVER! 
SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE DATABASE RemotePlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN RemotePlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN RemotePlay$owner DISABLE
DENY CONNECT SQL TO RemotePlay$owner
ALTER AUTHORIZATION ON DATABASE::RemotePlay TO RemotePlay$owner
go
--------------------------------------------------------------------------------------------
CREATE LOGIN [LIVERPOOL\SirPaul] FROM WINDOWS
CREATE LOGIN JohnL WITH PASSWORD = 'Revolution #9'
go
--------------------------------------------------------------------------------------------
USE RemotePlay
go
CREATE ROLE RemoteRole
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO RemoteRole 
CREATE USER JohnL
CREATE USER [LIVERPOOL\SirPaul]
ALTER ROLE RemoteRole ADD MEMBER JohnL
ALTER ROLE RemoteRole ADD MEMBER [LIVERPOOL\SirPaul]
go
--------------------------------------------------------------------------------------------
CREATE TABLE RemoteTable (id       int          NOT NULL,
                          somedata nvarchar(40) NOT NULL,
                          CONSTRAINT pk_Remote PRIMARY KEY (id))
INSERT RemoteTable(id, somedata)
   VALUES(1, 'Everybody''s Got Something to Hide'),
         (2, 'Except Me and My Monkey')
go
--==========================================================================================
-- ON LOCAL SERVER!
SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE DATABASE LocalPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN LocalPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN LocalPlay$owner DISABLE
DENY CONNECT SQL TO LocalPlay$owner
ALTER AUTHORIZATION ON DATABASE::LocalPlay TO LocalPlay$owner
go
--------------------------------------------------------------------------------------------
EXEC sp_addlinkedserver 'PLAYSERVER', N'', N'SQLNCLI11',
                        @datasrc = 'yournamehere'
go
--------------------------------------------------------------------------------------------
CREATE LOGIN Ringo WITH PASSWORD = 'Octopus''s Garden', 
                        DEFAULT_DATABASE = LocalPlay
go
--------------------------------------------------------------------------------------------
USE LocalPlay
go
CREATE ROLE LocalRole
GRANT EXECUTE ON SCHEMA::dbo TO LocalRole
CREATE USER Ringo
ALTER ROLE LocalRole ADD MEMBER Ringo
go
--------------------------------------------------------------------------------------------
CREATE PROCEDURE get_remote_data @id int AS
   SELECT id, somedata
   FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
   WHERE  id = @id
go
--==========================================================================================
-- Open a second window, where you log in as Ringo
EXEC get_remote_data 2
go
--==========================================================================================
-- Run in the first window as yourself.
USE LocalPlay
go
CREATE USER [LIVERPOOL\SirPaul]
go
ALTER PROCEDURE get_remote_data @id int 
   WITH EXECUTE AS 'LIVERPOOL\SirPaul' AS

   SELECT id, somedata
   FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
   WHERE  id = @id
go
--==========================================================================================
-- Run as Ringo!
EXEC get_remote_data 2
go
--==========================================================================================
-- Run as yourself.
USE LocalPlay
go
ALTER PROCEDURE get_remote_data @id int 
   WITH EXECUTE AS 'LIVERPOOL\SirPaul' AS

   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT id, somedata
                  FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
                  WHERE  id = @id'
   EXECUTE sp_executesql @sql, N'@id int', @id
go
--==========================================================================================
-- Run as Ringo!
EXEC get_remote_data 2
go
--==========================================================================================
-- Run as yourself.
ALTER PROCEDURE get_remote_data @id int AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT id, somedata
                  FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
                  WHERE  id = @id'

   EXECUTE AS LOGIN = 'LIVERPOOL\SirPaul'
      EXECUTE sp_executesql @sql, N'@id int', @id
   REVERT
go
--------------------------------------------------------------------------------------------
EXEC get_remote_data 1
go
--------------------------------------------------------------------------------------------
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN GeorgeH ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN GeorgeH DISABLE
DENY CONNECT SQL TO GeorgeH
go
--------------------------------------------------------------------------------------------
EXEC sp_addlinkedsrvlogin 'PLAYSERVER', 'false', 
                          'GeorgeH', 'JohnL', 'Revolution #9'
go
--==========================================================================================
USE LocalPlay
go
CREATE USER GeorgeH
go
ALTER PROCEDURE get_remote_data @id int AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT id, somedata
                  FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
                  WHERE  id = @id'
   EXECUTE AS LOGIN = 'GeorgeH'
      EXECUTE sp_executesql @sql, N'@id int', @id
   REVERT
go
--------------------------------------------------------------------------------------------
/* Open GrantPermsToSP_server.sql and sets the parameters:
DECLARE @database nvarchar(260) = 'LocalPlay',
        @procname nvarchar(520) = 'get_remote_data'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('IMPERSONATE ON LOGIN::GeorgeH')
*/
--==========================================================================================
-- Run as Ringo!
EXEC get_remote_data 2
go
--==========================================================================================
-- Run as yourself.
CREATE FUNCTION fun_remote_data(@id int) 
RETURNS @t TABLE (id       int          NOT NULL,
                  somedata nvarchar(40) NOT NULL)
  WITH EXECUTE AS 'GeorgeH' AS
BEGIN 
   INSERT @t (id, somedata)
      SELECT id, somedata
      FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
      WHERE  id = @id
  RETURN
END
go
CREATE PROCEDURE get_remote_data_alt @id int AS
   SELECT id, somedata
   FROM   dbo.fun_remote_data(@id)
go
--==========================================================================================
EXEC dbo.get_remote_data_alt 1      
--=========================================================================================
/* In the window with GrantPermsToSP_server.sql change the parameter part to:
DECLARE @database nvarchar(260) = 'LocalPlay',
        @procname nvarchar(520) = 'fun_remote_data'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('AUTHENTICATE SERVER')
*/
--=======================================================================================
EXEC dbo.get_remote_data_alt 1      
--==========================================================================================
-- Run as Ringo
EXEC get_remote_data_alt 1
go
--=======================================================================================
-- Run as yourself!
EXEC sp_recompile fun_remote_data
--=======================================================================================
-- Run as Ringo
EXEC get_remote_data_alt 1
go
--==========================================================================================
-- This is not in the article, but if you want to try it, run this as yourself.
CREATE FUNCTION fun_remote_data_inner(@id int) 
RETURNS @t TABLE (id       int          NOT NULL,
                  somedata nvarchar(40) NOT NULL)
BEGIN 
   INSERT @t (id, somedata)
      SELECT id, somedata
      FROM   PLAYSERVER.RemotePlay.dbo.RemoteTable
      WHERE  id = @id
  RETURN
END
go
ALTER FUNCTION fun_remote_data(@id int)
RETURNS @t TABLE (id       int          NOT NULL,
                  somedata nvarchar(40) NOT NULL)
  WITH EXECUTE AS 'GeorgeH' AS
BEGIN
   INSERT @t (id, somedata)
      SELECT id, somedata
      FROM   fun_remote_data_inner(@id)
   RETURN
END
go
--=========================================================================================
/* In the window with GrantPermsToSP_server.sql change the parameter part to:
DECLARE @database nvarchar(260) = 'LocalPlay',
        @procname nvarchar(520) = 'fun_remote_data'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('AUTHENTICATE SERVER')
*/
--=======================================================================================
-- Run as Ringo
EXEC get_remote_data_alt 1
--=======================================================================================
-- Run as yourself!
-- This is the cleanup.
USE master
go
ALTER DATABASE LocalPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE LocalPlay
go
EXEC sp_droplinkedsrvlogin 'PLAYSERVER', 'GeorgeH'
go
DROP LOGIN LocalPlay$owner
go
DROP LOGIN Ringo
go
DROP LOGIN "SIGN [LocalPlay].[dbo].[get_remote_data]"
go
DROP LOGIN "SIGN [LocalPlay].[dbo].[fun_remote_data]"
go
DROP CERTIFICATE "SIGN [LocalPlay].[dbo].[get_remote_data]"
go
DROP CERTIFICATE "SIGN [LocalPlay].[dbo].[fun_remote_data]"
go
DROP LOGIN GeorgeH
go
EXEC sp_dropserver 'PLAYSERVER'
go
--==========================================================================================
-- Cleanup on the remote server.
USE tempdb
go
ALTER DATABASE RemotePlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE RemotePlay
go
DROP LOGIN [LIVERPOOL\SirPaul]
go
DROP LOGIN JohnL
go
DROP LOGIN RemotePlay$owner
go