Search code examples
sql-serversecuritycode-signingsqlclrasymmetric

Cannot drop asymmetric key because there is a login mapped to it

I created an asymmetric key 'key_clr_http_request' and if I select * from sys.asymmetric_keys I can see it:

enter image description here

I now would like to delete it:

use master
DROP ASYMMETRIC KEY key_clr_http_request

But as a response I receive:

Cannot drop asymmetric key 'key_clr_http_request' because there is a login mapped to it.

I have already tried restarting SSMS and I'm sa on this test machine. No one is connecting to this machine right now, just me, so what is the login mapped?

Solution

  • This has nothing to do with your level of permissions, and certainly nothing to do with SSMS.

    This is how module signing works. The module (in this case the assembly) is signed with a strong name / asymmetric key. The public key portion of that strong name key gets loaded as an asymmetric key in [master]. From there, a login is created from that asymmetric key so that permissions can be assigned to it since permissions cannot be assigned to an asymmetric key or certificate. So, you need to drop the Login that was created from that asymmetric key, then you can drop the asymmetric key.

    Execute the following and you will see the login in question:

    SELECT aky.[name] AS [AsymmetricKey], lgn.*
FROM   sys.server_principals lgn
INNER JOIN sys.asymmetric_keys aky
        ON aky.[sid] = lgn.[sid];

    For more info on working with Module Signing, please visit: Module Signing Info

    For more info on working with SQLCLR in general, please visit: SQLCLR Info