You need to copy the certificate from master to your user database. For example:
-- Copy master certificate to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key varbinary(MAX) = certencoded(@cert_id),
@private_key varbinary(MAX) =
certprivatekey(@cert_id,
'your encryption password',
'your decryption password')
--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;
DECLARE @sql nvarchar(MAX) =
'CREATE CERTIFICATE your_certificate
FROM BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
WITH PRIVATE KEY (BINARY = ' +
convert(varchar(MAX), @private_key, 1) + ',
DECRYPTION BY PASSWORD = ''your encryption password'',
ENCRYPTION BY PASSWORD = ''your decryption password'')'
EXEC YourUserDatabase.sys.sp_executesql @sql;
EDIT
If the certificate is encrypted with the database master key instead of a password, omit the decryption password from CERTPRIVATEKEY
(to decrypt with the source database DBMK) and omit the ENCRYPTION BY PASSWORD
clause of CREATE CERTIFICATE
(to encrypt with the target database DBMK). The password in this case is used only to encrypt the private key in transit.
-- Copy cert to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key varbinary(MAX) = CERTENCODED(@cert_id),
@private_key varbinary(MAX) =
CERTPRIVATEKEY(@cert_id,
'your encryption password');
--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;
--create certificate in target database encrypted with DBMK
DECLARE @sql nvarchar(MAX) =
'CREATE CERTIFICATE your_certificate
FROM BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
WITH PRIVATE KEY (BINARY = ' +
convert(varchar(MAX), @private_key, 1)
+ ', DECRYPTION BY PASSWORD = ''your encryption password'');'
PRINT @sql
EXEC YourUserDatabase.sys.sp_executesql @sql;
GO
I'll add that for only cross-database permissions, one doesn't need to create a login from the certificate. Create a certificate in a user database, copy the cert to other databases as needed, create a user from the certificate in each database and grant permissions to the cert user, and finally sign the proc with the certificate.
A server level principal is needed only when server-level permissions must be granted.