主页 / dba / 问题 / 20250
Accepted
Filip De Vos
Asked: 2012-07-03 03:18:06 +0800 CST

什么可能触发从证书创建的登录在元数据中不可见

  • 772

我正在尝试创建一个登录名并授予它运行和不安全程序集的权限，但是当它已经存在时，对 server_principals 的检查不会返回登录名。该脚本在我的本地开发服务器上运行良好，但在我们的测试服务器上失败：

  • 我的开发服务器：Sql Server 2008 R2 SP1 --> 工作正常
  • 远程服务器 1：Sql Server 2005 SP4 --> 错误
  • 远程服务器 2：Sql Server 2008 R2 RTM --> 错误

我的用户是sysadmin 角色的成员。

该脚本将创建一个证书（真正的交易从 .net 程序集中提取证书），检查登录是否存在，如果不存在，它将创建登录并授予不安全的程序集权限。

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

此脚本返回的错误如下：

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.

由于我的用户是所有服务器上的系统管理员，我真的很困惑为什么会这样（除非它是 SQL 2008 R2 SP1 中修复的错误）。

编辑：“Quotename”中的包装对象名称

编辑：添加了 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

编辑：添加了 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
sql-server sql-server-2005

2 个回答

  1. 这段代码为我执行得很好。我可能怀疑的唯一问题是您的服务器区分大小写。EXISTS如果您有一个名为的登录名，则该检查将为空，cert01login但创建将失败，因为它不区分大小写。因此，您需要确保 EXISTS 检查不区分大小写，例如

    WHERE name COLLATE Latin1_General_CI_AI = @cert_name + 'Login'
    • 0
  2. Best Answer

    证书（这是从程序集中提取的 Comodo 证书）已经映射了一个登录名。这是错误的原因。（在我的本地服务器上并非如此）。

    这是重现错误的重现脚本：

    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

    为防止将来出现此问题，应将脚本更改为：

    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

    我确实认为错误消息很糟糕，因为很容易找到映射的登录名。我记录了以下连接问题。

    • 0

相关问题