Re: Permission to view system tables in master database



Dave (Dave@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
That is a very useful web site at http://www.sommarskog.se/

However, one thing I am not clear on; why do you back up the private key
and Laurentiu drops it? What is the risk of dropping it like he did?


Erland Sommarskog's example:

BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')


Laurentiu Cristofor's example:

alter certificate certSignCreatePrincipal remove private key;
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';

If I do it in one way, and Laurentiu another, Laurentiu is likely to be
right! (Unless it's the usage of EXECUTE AS, where we are known to
disagree. :-)

I will have to admit that I was not aware of the REMOVE PRIVATE KEY
clause. I used what I was able to get to work. Dropping the private key
seems to be a better idea. However, as I understand it, it calls for a
different order of things. To wit, I first create the certificate and the
login in master, export the cert, and then move to the target database
where I sign the procedure. As I need the privte key to sign, I do need
the private key at this point.

I should definitely spend some time reworking the example - I only
need to find that time. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.