USE [master];
-- Create certificate in master.
CREATE CERTIFICATE SelfAdminCert
	ENCRYPTION BY PASSWORD = 'Password'
	WITH SUBJECT = 'Grants some server level permissions to specified procedures',
	START_DATE = '20130101', EXPIRY_DATE = '20181231'
;
GO
-- Create a login for the certificate.
CREATE LOGIN SelfAdminAI FROM CERTIFICATE SelfAdminCert;
GO
-- Grant rights for the certificate login.
EXEC sys.sp_addsrvrolemember 'SelfAdminAI', 'dbcreator';
GO
-- Save the certificate to disk.
BACKUP CERTIFICATE ITImpactIncSelfAdminCert TO FILE = 'C:\Temp\Cert.cer'
WITH PRIVATE KEY (
	FILE = 'C:\Temp\Cert.pvk',
	ENCRYPTION BY PASSWORD = 'Password',
	DECRYPTION BY PASSWORD = 'Password'
);
GO
USE [MyDatabase];
-- Import the certificate we created in master into the test database.
CREATE CERTIFICATE SelfAdminCert FROM FILE = 'C:\Temp\Cert.cer'
WITH PRIVATE KEY (
	FILE = 'C:\Temp\Cert.pvk',
	DECRYPTION BY PASSWORD = 'Password',
	ENCRYPTION BY PASSWORD = 'Password'
);
GO
-- And create a user for the certificate.
CREATE USER SelfAdminAI FOR CERTIFICATE SelfAdminCert;
go
-- Grant this user rights.
EXEC sp_addrolemember db_backupoperator, SelfAdminAI;
go
-- Sign the test procedures.
ADD SIGNATURE TO dbo.uspCopyToBeta BY CERTIFICATE SelfAdminCert
WITH PASSWORD = 'Password';