SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE SERVER ROLE SafeAssemblyLoaders
CREATE SERVER ROLE ExtAccessAssemblyLoaders
CREATE SERVER ROLE UnsafeAssemblyLoaders
ALTER SERVER ROLE SafeAssemblyLoaders ADD MEMBER UnsafeAssemblyLoaders
ALTER SERVER ROLE SafeAssemblyLoaders ADD MEMBER ExtAccessAssemblyLoaders
go
----------------------------------------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE sp_play_check_permission 
                          @permission_set varchar(20) AS
   -- For diagnostics only.
   -- SELECT name, usage, type FROM sys.login_token

   -- Check that user has permission to create assemblies in this database.
   DECLARE @db sysname = db_name()
   IF isnull(has_perms_by_name(@db, 'DATABASE', 'CREATE ASSEMBLY'), 0) = 0
   BEGIN
      RAISERROR('You do not have CREATE ASSEMBLY permission in database "%s".', 
                16, 1, @db)
   END

   -- And check that user is entitled to create assemblies of this type.
   IF has_perms_by_name(NULL, NULL, 'CONTROL SERVER') = 0 AND
      isnull(
         is_srvrolemember(CASE upper(@permission_set)
                               WHEN 'SAFE'            THEN 'Safe'
                               WHEN 'EXTERNAL ACCESS' THEN 'ExtAccess'
                               WHEN 'UNSAFE'          THEN 'Unsafe'
                          END + 'AssemblyLoaders', SYSTEM_USER), 0) = 0
   BEGIN
      RAISERROR('You do not have permission to load %s assemblies',
                16, 1, @permission_set)
   END
go
----------------------------------------------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE sp_play_load_assembly 
                          @srcpath        nvarchar(250),
                          @assemname      sysname,
                          @permission_set varchar(20) = 'SAFE' AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY

   -- For diagnostics only.
   -- SELECT name, usage, type FROM sys.login_token

   -- Verify @permission_set.
   IF upper(@permission_set) IS NULL OR
      @permission_set NOT IN ('SAFE', 'UNSAFE', 'EXTERNAL ACCESS')
   BEGIN 
      RAISERROR('Illegal value for @permission_set: "%s".', 
                16, 1, @permission_set)
      RETURN 1
   END

   -- Verify @srcpath - it must end in .cs.
   IF @srcpath IS NULL OR lower(right(@srcpath, 3)) <> '.cs'
   BEGIN 
      RAISERROR('Illegal value for @srcpath "%s". Does not end in .cs', 
                16, 1, @srcpath)
      RETURN 1
   END

   -- Check that the current user has permission to assemblies of the
   -- given type. Error is raised if not.
   EXEC sp_play_check_permission @permission_set
     
   -- Derive the path for the assembly.
   DECLARE @assempath nvarchar(250) = 
          substring(@srcpath, 1, len(@srcpath) - 3) + '.dll'

   -- Set up path for the C# compiler, you may need to adopt this.
   DECLARE @cscpath nvarchar(250) = 
          'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe' 

   -- Build command to run source file.
   DECLARE @cmd varchar(2000) = @cscpath + ' /nologo /target:library /out:' + 
                                '"' + @assempath + '" "' + @srcpath + '"'
   PRINT @cmd
   DECLARE @ret int
   EXEC @ret = xp_cmdshell @cmd
   IF @ret <> 0
   BEGIN 
      RAISERROR('Compilation failed, check result set for details.', 16, 1)
      RETURN @ret
   END

   -- Build command to load DLL from file.
   DECLARE @sql nvarchar(MAX),
           @assem_bits varbinary(MAX)
   SELECT @sql = 'SELECT @bits = A.bits
                  FROM   OPENROWSET(BULK ' + quotename(@assempath, '''') + 
                 ', SINGLE_BLOB) AS A(bits)'
   PRINT @sql
   EXEC sp_executesql @sql, N'@bits varbinary(MAX) OUTPUT', @assem_bits OUTPUT

   -- Compute hash.
   DECLARE @hash varbinary(64) = hashbytes('SHA2_512', @assem_bits)

   -- For description line for sys.trusted_assemblies.
   DECLARE @description nvarchar(4000) = quotename(db_name()) + '.' + 
                                         quotename(@assemname)

   EXEC sp_add_trusted_assembly @hash, @description

   -- Now we can create it.
   SELECT @sql = CASE WHEN assemblyproperty(@assemname, 'CLRName') IS NULL
                      THEN 'CREATE'
                      ELSE 'ALTER'
                  END + ' ASSEMBLY ' + quotename(@assemname) + ' FROM @bits ' +
                        'WITH PERMISSION_SET = ' + @permission_set
   PRINT @sql
   EXEC sp_executesql @sql, N'@bits varbinary(MAX)', @assem_bits

   -- To avoid littering, we drop existing hash for this assembly.
   SELECT @sql = STRING_AGG('EXEC sp_drop_trusted_assembly ' + 
                             convert(varchar(200), hash, 1), char(13) + char(10))
   FROM   sys.trusted_assemblies
   WHERE  description = @description
     AND  hash <> @hash
   IF @sql IS NOT NULL
   BEGIN 
      PRINT @sql
      EXEC(@sql)
   END
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
go
--------------------------------------------------------------------------------------------------------
CREATE CERTIFICATE sp_play_load_assembly$cert
   ENCRYPTION BY PASSWORD = 'We Can Work it Out'
   WITH SUBJECT = '"SIGN sp_play_load_assembly GRANT CONTROL SERVER"'
CREATE LOGIN sp_play_load_assembly$certlogin 
      FROM CERTIFICATE sp_play_load_assembly$cert
GRANT CONTROL SERVER TO sp_play_load_assembly$certlogin
go
ADD SIGNATURE TO sp_play_load_assembly 
    BY CERTIFICATE sp_play_load_assembly$cert
    WITH PASSWORD = 'We Can Work it Out'
go
GRANT EXECUTE ON sp_play_load_assembly TO public
go
-----------------------------------------------------------------------------------------------
EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 1
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
go
---------------------------------------------------------------------------------------------------------
CREATE DATABASE AssemPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN AssemPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN AssemPlay$owner DISABLE
DENY CONNECT SQL TO AssemPlay$owner
ALTER AUTHORIZATION ON DATABASE::AssemPlay TO AssemPlay$owner
go
------------------------------------------------------------------------------------------------------
CREATE LOGIN MrMustard WITH PASSWORD = 'Shaves in the dark trying to save paper'
ALTER SERVER ROLE SafeAssemblyLoaders ADD MEMBER MrMustard
go
USE AssemPlay
go
CREATE USER MrMustard
ALTER ROLE db_owner ADD MEMBER MrMustard
go
------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'MrMustard'
go
EXEC sp_play_load_assembly 'C:\temp\democlr.cs', 'DemoClr', 'SAFE'
go
CREATE OR ALTER PROCEDURE DemoClr AS
   EXTERNAL NAME DemoClr.StoredProcedures.democlr
go
EXEC DemoClr
go
REVERT
go
-----------------------------------------------------------------------------------------------
-- Introduce a compilation error in democlr.cs:
EXEC sp_play_load_assembly 'C:\temp\democlr.cs', 'DemoClr', 'SAFE'
go
-----------------------------------------------------------------------------------------------
USE master
go
EXECUTE AS LOGIN = 'MrMustard'
EXEC sp_play_load_assembly 'C:\temp\democlr.cs', 'DemoClr', 'SAFE'
go
REVERT
USE AssemPlay
go
-----------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'MrMustard'
go
EXEC sp_play_load_assembly 'C:\temp\sunking.cs', 'SunKing', 'SAFE'
go
REVERT
go
-----------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'MrMustard'
go
EXEC sp_play_load_assembly 'C:\temp\sunking.cs', 'SunKing', 'UNSAFE'
go
REVERT
go
------------------------------------------------------------------------------------------------
EXEC sp_play_load_assembly 'C:\temp\sunking.cs', 'SunKing', 'UNSAFE'
go
CREATE OR ALTER FUNCTION SunKing () RETURNS bigint AS
   EXTERNAL NAME SunKing.sun.sunking 
go
SELECT dbo.SunKing()
SELECT dbo.SunKing()
SELECT dbo.SunKing()
--------------------------------------------------------------------------------------------------
SELECT * FROM sys.trusted_assemblies
go
----------------------------------------- CLEANUP -------------------------------------------------
USE master
go
ALTER DATABASE AssemPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE AssemPlay
go
DROP LOGIN AssemPlay$owner
go
DROP PROCEDURE sp_play_load_assembly
go
DROP PROCEDURE sp_play_check_permission
go
DROP LOGIN MrMustard
go
DROP SERVER ROLE UnsafeAssemblyLoaders
go
DROP SERVER ROLE ExtAccessAssemblyLoaders
go
DROP SERVER ROLE SafeAssemblyLoaders
go
DROP LOGIN sp_play_load_assembly$certlogin
go
DROP CERTIFICATE sp_play_load_assembly$cert
go
DECLARE @sql nvarchar(MAX)
SELECT @sql = STRING_AGG('EXEC sp_drop_trusted_assembly ' + 
                           convert(varchar(200), hash, 1), char(13) + char(10))
FROM   sys.trusted_assemblies
WHERE  description LIKE '%AssemPlay%'
PRINT @sql
EXEC(@sql)
go
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE