improve error message of "Create login from certificate" - by Filip De Vos

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 752111 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 7/3/2012 12:44:49 AM
Access Restriction Public


When creating a second login from a certificate the error message is unclear on what the problem is:

When creating a login "Cert01Login" from a certificate which already has a login mapped to it the error message is the following:

  Msg 15025, Level 16, State 2, Line 2
  The server principal 'Cert01Login' already exists.

The script below can be used to reproduce this error message:

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)
    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'';')

IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = @login_name)
    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 + ';')
Sign in to post a comment.
Posted by Filip De Vos on 10/15/2013 at 3:50 AM
I checked on SQL 2014 CTP2 and it works. Error message is now:

Msg 33140, Level 16, State 1, Line 3
The login 'Cert01Login2' could not be created because a login is already associated with certificate 'Cert01'.

Thanks for fixing this.
Posted by Fang [MSFT] on 6/28/2013 at 1:01 PM
Hi Filip,

We have added an additional error message to distinguish between the different situations. The fix will be shipped in the next major release of SQL Server.

Thank you very much for your input and let us know if there are any other concerns.

Fang Hou
Software Development Engineer - SQL Server Tiger Team
Posted by Microsoft on 4/25/2013 at 10:12 AM
Hi Filip,

Thank you for bringing this to our attention. We'll take a look at it and let you know what we decide to do.