Is it possible to add a certificate to a database role?

Question

I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using:

CREATE CERTIFICATE testCertificate
FROM FILE = 'test_certificate6.cer';

CREATE USER testCertificateUser 
FROM CERTIFICATE testCertificate;

GRANT AUTHENTICATE TO testCertificateUser;
GRANT EXECUTE ON storedProc1 TO testCertificateUser;

but need to be able to add the inter-DB access to an existing database role (for compatibility with existing systems). How can I go about doing this?

Answer 1

No, it is not possible to create a Role from a CERTIFICATE or ASYMMETRIC KEY like you can with Logins and Users. It is also not necessary to do this nor would it even make sense to do this. And the reason that is doesn't make sense to associate a Role with a Certificate or Asymmetric Key is that this way of doing security does not work like the standard way of granting permissions.

In the typical / common way of granting permissions, you give permission to a User / Login / Role to do a particular action (Authenticate / Insert / Create / Update / Select / Execute / etc), often on a particular object (i.e. Schema / Table / View / Function / etc). Here you are allowing someone (or some process) to do (or not do) the action that they are requesting to do.

But Certificate- / Asymmetric Key- based security is not concerned with who is making the request, but what code is making the request. This is where ADD SIGNATURE comes in since it makes the association between the code making the request and what permissions that code has. But code (i.e. Stored Procedures, Scalar T-SQL Functions, Multi-Statement T-SQL Functions, Triggers, and SQLCLR objects via the Assembly) cannot be granted (or denied) any permissions directly. Hence you need to create a Login and/or User from the Certificate or Asymmetric Key to receive those permissions and act as a proxy for those permissions to whatever code has the same Certificate or Asymmetric Key that was used to create the Login and/or User. And Logins and Users created from Certificates and Asymmetric Keys cannot log in since they are merely vessels for those proxy permissions; they do not make requests as themselves.

So, if you want a Stored Procedure to access one particular DMV that requires VIEW SERVER STATE, but you don't want to:

• grant that permission to a Login because it gives access to a lot of other DMVs, or
• use the EXECUTE AS statement since it requires that the User / Login -- the one who should be restricted -- has the IMPERSONATE permission for whichever User / Login they need to "execute as". This leaves the door open to use EXECUTE AS to do other things beyond this desired action, or
• use the EXECUTE AS Clause because you either do not want to change the actual execution context (you just want to grant additional permissions), or you need to do something that spans databases, or there is some other nuance of the EXECUTE AS clause that is causing issues,

then you would:

1. Create the Stored Procedure that SELECTs from that DMV

2. Create a Certificate or Asymmetric Key

3. Create a Login from that Certificate or Asymmetric Key

4. Grant that Login the VIEW SERVER STATE permission

5. Use ADD SIGNATURE with the same Certificate or Asymmetric Key on that Stored Procedure created in Step 1.

   This final step ties the Login (having the correct permission) to that Stored Procedure. Now the Stored Procedure itself, not any Login or User executing that Stored Procedure, has whatever permissions were given to the Login that has the same Certificate or Asymmetric Key.

So, if you have a Role that was used to grant object permissions to for a particular database, then you should just need to add the Certificate-based User to that Role, just like you would add any other User to that Role to grant them the permissions assigned to that Role. This is similar to how you granted EXECUTE on the stored procedure to the Certificate-based User, just like you would for other Users / Roles.