No, it is not possible to create a Role from a
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:
Create the Stored Procedure that
SELECTs from that DMV
Create a Certificate or Asymmetric Key
Create a Login from that Certificate or Asymmetric Key
Grant that Login the
VIEW SERVER STATE permission
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.