Service Broker Users & Certificates

Question

Hi,

I'm getting up to speed on Service Broker for multple SQL instance scenarios.  The tutorials in Books Online (2008 R2) have the owner user for a service set up as something like this:

CREATE USER SvcUsr WITHOUT LOGIN;
CREATE CERTIFICATE SvcUsrCert
  AUTHORIZATION SvcUsr
  WITH SUBJECT = 'Server.DB.SvcUsr', EXPIRY_DATE = N'12/31/2020';
BACKUP CERTIFICATE SvcUsrCert
  TO FILE = N'X:\...\SvcUsrCert.cer;

What is the functional difference between that (creating a user and setting it as the owner of a certificate) and this (creating a user from a certificiate):

CREATE CERTIFICATE SvcUsrCert
  AUTHORIZATION SvcUsr
  WITH SUBJECT = 'Server.DB.SvcUsr', EXPIRY_DATE = N'12/31/2020';
BACKUP CERTIFICATE SvcUsrCert
  TO FILE = N'X:\...\SvcUsrCert.cer;
CREATE USER SvcUsr FOR CERTIFICATE SvcUsrCert;

Thank you in advance.

---

Thank you, Dan

Answer 1

Hi Dan,

Both two functions are used to authorize the user SvcUsr as the owner of the certificate SvcUsrCert by the AUTHORIZATION keyword. You can view this information via:

SELECT name, user_name(principal_id) as cert_owner  FROM sys.certificates

You can alter the owner of a certificate as:

ALTER AUTHORIZATION ON CERTIFICATE:: <certificatename>  TO <username>

The difference between these two functions is the way how the user is created. For the first, the user is created without any login. For the second, the user is created for this certificate, which means that this user can only be mapped to this certificate. Assuming that you create the user SvcUsr for CERTIFICATE SvcUsrCert, then you cannot authorize this user to another certificate as:

ALTER AUTHORIZATION ON CERTIFICATE:: <othercertificate>  TO < SvcUsr >

However, you can authorize more than one certificates for the user created by the first way. In addition, you can only create one user for each certificate.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

Stephanie,

I understand your answer, but it hightlighted that I got carried away with copy & pasting when I created my second code block from my first.  Looking at it now, the second code block would crash, because user SvcUsr (specified in the AUTHORIZATION clause) does not exist when the CREATE CERTIFICATE statement is executed first.  Please verify that the gist of your answer is not really affected when my second code block is corrected to:

 

CREATE CERTIFICATE SvcUsrCert
  WITH SUBJECT = 'Server.DB.SvcUsr', EXPIRY_DATE = N'12/31/2020';
BACKUP CERTIFICATE SvcUsrCert
  TO FILE = N'X:\...\SvcUsrCert.cer;
CREATE USER SvcUsr FOR CERTIFICATE SvcUsrCert;

Again, 

---

Thank you, Dan

Answer 3

Dan,

For the second code block in your first post, you need to create the user first and then create the certificate to authorize this user. However, if you want to create a user for a certificate, the certificate should be existed first. So it is not possible to create a user for a certificate, at the same time, you want to authorize this user while you creating this certificate.

Another way to realize this is to create the certificate without specified the AUTHORIZATION, in this case, the user ‘dbo’ will be authorized as the owner of this certificate. Then create the user for this certificate. Finally, alter the certificate to authorize this user as the owner. Please view the syntax below:

CREATE CERTIFICATE SvcUsrCert
 -- AUTHORIZATION SvcUsr
  WITH SUBJECT = 'Server.DB.SvcUsr', EXPIRY_DATE = N'12/31/2020';
BACKUP CERTIFICATE SvcUsrCert
  TO FILE = N'X:\...\SvcUsrCert.cer';
  
CREATE USER SvcUsr FOR CERTIFICATE SvcUsrCert;

ALTER AUTHORIZATION ON CERTIFICATE::SvcUsrCert TO SvcUsr

---

Stephanie Lv

TechNet Community Support

Answer 4

I guess my question boils down to this:  If the identity of a user can be certified simply by making it the owner of a certificate, why might one create a user from a certificate?

 

---

Thank you, Dan

Answer 5

Dan,

The usage of creating a user for a certificate is used to narrow down the scope of this user, which can only be used to map to this certificate. In my opinion, the purpose here is manageable, for example, you do not need to worry about other relationships for this user if you want to drop the certificate along with this user.

---

Stephanie Lv

TechNet Community Support