Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
Don't use manual [ and ]. Use QUOTENAME becuase it correctly handles names with embeded ]... –  Remus Rusanu Jul 2 '12 at 12:27
    
@RemusRusanu while true, if someone is naming a login with an embedded ] they deserve for this to fail. :-) –  Aaron Bertrand Jul 2 '12 at 12:53
    
I should have used Quotename. Updating the sample script. –  Filip De Vos Jul 2 '12 at 13:12
2  
Please use sys.server_principals -- sys.syslogins is deprecated and should not be used for new development work. It's possible there's some kind of compatibility issue going on here. –  Jon Seigel Jul 2 '12 at 13:27
    
@AaaronBertrand: the one which would create such a name may not be an innocent. This is an escalation of priviledge attack vector, someone with create login priviledge can escalate himself to sysadmin by creating such a login and waiting for a sysadmin to run this script... –  Remus Rusanu Jul 2 '12 at 13:43

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
Mark this as the answer, so it will not show up as "UnAnswered". –  RBarryYoung Jul 2 '12 at 21:56
    
I can only do that in another 2 days. –  Filip De Vos Jul 2 '12 at 22:00
    
Sorry, I did not realize that there was a waiting period. –  RBarryYoung Jul 2 '12 at 22:08

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'
share|improve this answer
    
It is a Latin1_General_BIN server, but this is not the cause of the problem. (Same error) –  Filip De Vos Jul 2 '12 at 13:08
    
Can you show the names that are actually stored in sys.server_principals so we don't have to guess? While we can see that the EXISTS check is failing, it's obvious that there is a conflict. –  Aaron Bertrand Jul 2 '12 at 13:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.