Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


2
1
Sign in
to vote
Type: Suggestion
ID: 752111
Opened: 7/3/2012 12:44:49 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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)
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
Details
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 Microsoft 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.

Thanks!
Sign in to post a workaround.