USE master
go
-- Create a test file for bulk load.
EXEC xp_cmdshell 'ECHO 978,123,234 > C:\temp\reloadtest.csv', no_output
EXEC xp_cmdshell 'ECHO -98,13,85 >> C:\temp\reloadtest.csv', no_output
go
-- Create a test login.
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
go
-- Create test database.
CREATE DATABASE bulkcerttest
go
-- Create certificate in master.
CREATE CERTIFICATE reloadcert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'For bulk-load privileges',
   START_DATE = '20020101', EXPIRY_DATE = '20200101'
go
-- Create a login for the certificate.
CREATE LOGIN reloadcert_login FROM CERTIFICATE reloadcert
go
-- Grant rights for the certificate login.
GRANT ADMINISTER BULK OPERATIONS TO reloadcert_login
go
-- Get the certificate bytes into a temp table so we can use it in 
-- the target database.
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL,
                    privkey varbinary(MAX) NOT NULL)
INSERT #keys (pubkey, privkey)
   SELECT certencoded(cert_id('reloadcert')),
          certprivatekey(cert_id('reloadcert'), 'Tomorrow never knows',
                                                'All you need is love')
go
-- Move to test database.
USE bulkcerttest
go
-- Create the non-priv user.
CREATE USER testuser
go
-- A test table.
CREATE TABLE reloadable (a int NOT NULL,
                         b int NOT NULL,
                         c int NOT NULL)
go
-- Insert some test data. If test succeeds, this data should disappear.
INSERT reloadable (a, b, c) VALUES (12, 23, 34)
go
-- Test procedure with BULK INSERT. BULK INSERT needs to be in
-- EXEC() because of a bug in SQL Server.
CREATE PROCEDURE reload_sp AS
    SELECT name, type, usage FROM sys.login_token
    SELECT name, type, usage FROM sys.user_token
    TRUNCATE TABLE reloadable
    EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv''
          WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')')
go
-- The same code, but this procedure we will not sign.
CREATE PROCEDURE unsigned_sp AS
    SELECT name, type, usage FROM sys.login_token
    SELECT name, type, usage FROM sys.user_token
    --TRUNCATE TABLE reloadable
    EXEC('BULK INSERT reloadable FROM ''C:\temp\reloadtest.csv''
          WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')')
go
-- Give test user right to execute the procedures.
GRANT EXECUTE ON reload_sp TO testuser
GRANT EXECUTE ON unsigned_sp TO testuser
go
-- Import the certificate we created in master into the test database.
DECLARE @sql nvarchar(MAX)
SELECT @sql = 
   'CREATE CERTIFICATE reloadcert 
    FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + '
    WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ',
                      DECRYPTION BY PASSWORD = ''Tomorrow never knows'',
                      ENCRYPTION BY PASSWORD = ''A day in life'')'
FROM #keys

PRINT @sql
EXEC (@sql)
DROP TABLE #keys
go
-- And create a user for the certificate.
CREATE USER reloadcert_user FOR CERTIFICATE reloadcert
go
-- Grant this user rights to truncate and insert to the test table.
GRANT ALTER, INSERT ON reloadable TO reloadcert_user
go
-- Sign the test procedures.
ADD SIGNATURE TO reload_sp BY CERTIFICATE reloadcert
    WITH PASSWORD = 'A day in life'
go
-- Switch to the test user.
EXECUTE AS LOGIN = 'testuser'
go
-- Run the unsigned procedure. You will get a permission error.
EXEC unsigned_sp
go
-- Run the real reload procedure.
EXEC reload_sp
go
-- Back to ourselves.
REVERT
go
-- The data in the table has been replaced.
SELECT a, b, c FROM reloadable
go
-- Clean up.
USE master
go
DROP DATABASE bulkcerttest
DROP LOGIN reloadcert_login
DROP CERTIFICATE reloadcert
DROP LOGIN testuser
EXEC xp_cmdshell 'DEL C:\temp\reloadtest.csv', 'no_output'