What could trigger that a login created from a certificate is not visible in metadata

Question

I am trying to create a login and grant it rights to run and unsafe assembly but the check on server_principals does not return the login when it already exists. The script works perfectly fine on my local development server, but fails on our test servers:

• My dev server: Sql Server 2008 R2 SP1 --> works ok
• Remote server1: Sql Server 2005 SP4 --> error
• Remote server2: Sql Server 2008 R2 RTM --> error

My user is a member of the sysadmin role.

The script will create a certificate (The real deal extracts the certificate from a .net assembly), check if the login exists and if not, it will create the login and grant unsafe assembly rights.

declare @cert_name sysname
      , @safe_cert_name sysname
      , @trust_level nvarchar(20)
      , @login_name sysname

select @cert_name = N'Cert01'
     , @trust_level = N'unsafe'
     , @login_name = QUOTENAME(@cert_name + N'Login')

select @safe_cert_name = QUOTENAME(@cert_name)

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'USE master;
       CREATE CERTIFICATE ' + @safe_cert_name + N' 
       ENCRYPTION BY PASSWORD = ''omg00001111;!@#$%^&''
       WITH SUBJECT = ''Test Certificate you can drop me'', 
       EXPIRY_DATE = ''20120801'';')
   END


IF NOT EXISTS(SELECT * FROM master.sys.server_principals WHERE name = @cert_name + 'Login')
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

The error returned by this script is the following:

Creating login [Cert01Login] from certificate [Cert01]
Msg 15025, Level 16, State 2, Line 2
The server principal 'Cert01Login' already exists.
Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'Cert01Login', because it does not exist or you do not have permission.

Since my user is sysadmin on all servers I am really puzzled why this is behaving like this (unless it is a bug fixed in SQL 2008 R2 SP1).

Edit: Wrapped object names in "Quotename"

Edit: Added content of server_principals

SELECT name 
  FROM sys.server_principals 
 ORDER BY name

name
=========================================
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##
##MS_SQLAuthenticatorCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLResourceSigningCertificate##
##MS_SmoExtendedSigningCertificate##
OURDOMAIN\ACR
...
OURDOMAIN\MYUSER
...
OURDOMAIN\ZVA
NT AUTHORITY\SYSTEM
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
SQL_Linker
archiving
bulkadmin
dbcreator
diskadmin
processadmin
public
sa
securityadmin
serveradmin
setupadmin
sysadmin

Edit: Added output of sp_helpsrvrolemember

exec sp_helpsrvrolemember 'sysadmin'

ServerRole  MemberName                MemberSID
==========  ===========               =========
sysadmin    sa                        0x01
sysadmin    NT AUTHORITY\SYSTEM       0x010100000000000512000000
sysadmin    NT SERVICE\MSSQLSERVER    0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E
sysadmin    NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357
sysadmin    OURDOMAIN\ADMINS          0x0105000000000005150000003301161E3D17782BC63FD75B922C0000
sysadmin    OURDOMAIN\MYUSER          0x0105000000000005150000003301161E3D17782BC63FD75B8B150000

Answer 1

This code executed for me fine. The only issue I might suspect is that your server is case sensitive. The EXISTS check will come up empty if you have a login named cert01login but the create will fail because it is not case sensitive. So you'll want to make sure the EXISTS check is not case sensitive, e.g.

WHERE name COLLATE Latin1_General_CI_AI = @cert_name + 'Login'

Answer 2

The certificate (which is a Comodo cert extracted from an assembly) already had a login mapped to it. This is the cause of the error. (On my local server this was not the case).

This is the repro script which reproduces the error:

declare @cert_name sysname
      , @safe_cert_name sysname
      , @trust_level nvarchar(20)
      , @login_name sysname
      , @login2_name sysname

select @cert_name = N'Cert01'
     , @trust_level = N'unsafe'
     , @login_name = QUOTENAME(@cert_name + N'Login')
     , @login2_name = QUOTENAME(@cert_name + N'Login2')

select @safe_cert_name = QUOTENAME(@cert_name)

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'USE master;
       CREATE CERTIFICATE ' + @safe_cert_name + N' 
       ENCRYPTION BY PASSWORD = ''omg00001111;!@#$%^&''
       WITH SUBJECT = ''Test Certificate you can drop me'', 
       EXPIRY_DATE = ''20120801'';')
   END

IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = @login_name)
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          CREATE LOGIN ' + @login2_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

To prevent this issue in the future, the script should be altered to:

IF NOT EXISTS(SELECT * 
                FROM sys.server_principals p 
                JOIN master.sys.certificates c 
                  ON p.sid = c.sid
               WHERE c.name = @cert_name)
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

I do think that the error message is quite bad seeing that it is very easy to find the mapped login. I logged the following connect issue over it.