SQL查询错误: SQLDMO

Question

下面是我的SQL查询，它在SQL server 2005上运行良好，但是在Server 2008上，它引发错误：

sp_Security错误:无法创建Server对象

SQL查询：

DECLARE @object int
DECLARE @hr int
DECLARE @hack smallint
DECLARE @return varchar(255)
DECLARE @Results nvarchar(255)
DECLARE @server sysname
DECLARE @login sysname
DECLARE @tsql varchar(1500)

set @server = 'YOGESH\SQLEXPRESS'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr < 0
BEGIN
   RAISERROR('sp_Security error: Unable to create SQLDMO Server object', 0, 1)
   RETURN
END

EXEC @hr = sp_OASetProperty @object,'LoginSecure', 'False'

IF @hr < 0
BEGIN
   RAISERROR('sp_Security error: Unable to set LoginSecure', 0, 1)
   GOTO ExitProc
END

Print ' Security Audit for Server : ' + @server

set @tsql = 'DECLARE login_cursor CURSOR FOR SELECT loginname FROM master.dbo.syslogins order by loginname' 

exec (@tsql)

OPEN login_cursor

FETCH NEXT FROM login_cursor
INTO @login

WHILE @@FETCH_STATUS = 0
BEGIN
   set @hack = 0
   set @Results = 'Connect("' + @server +'","'+@login+'","'+@login+'")'

   EXEC @hr = sp_OAMethod @object, @Results

   IF @hr = 0
   begin
      print 'login : ' + @login + ' security problem with password ' + @login
      EXEC @hr = sp_OAMethod @object, 'Disconnect'

      set @hack = 1
   end

   IF (@hack = 0)
   begin
     set @Results = 'Connect("' + @server +'","'+@login+'","'+'")'

     EXEC @hr = sp_OAMethod @object, @Results

     IF @hr = 0
     begin
        print 'login : ' + @login + ' security problem with password NULL'

        EXEC @hr = sp_OAMethod @object, 'Disconnect'

        set @hack = 1
     end
   end

Answer 1

正如“硬数学评论”中所引用的那样，微软不再使用Server 2008分发SMO库来支持SMO。即便如此，您仍然可以通过“Microsoft 2005向后兼容性组件”安装SQL (您可以在此页上找到该软件包的下载链接)。

以下是启用不使用SQL的Server登录的另一种方法：

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

我怀疑上述代码不会在Server 2008之前的Server版本上工作。