SQL Query Error: SQLDMO

Question

The following is my SQL query which works fine on SQL server 2005, but on SQL Server 2008, it throws the error:

sp_Security error: Unable to create SQLDMO Server object

SQL Query:

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

Possible duplicate: A [similar question on SQLDMO in SqlServer 2008](http://stackoverflow.com/questions/2875145/sql-server-2008-alternative-for-sql-dmo) was posted (and answered) in 2010. See [Server Management Objects](http://msdn.microsoft.com/en-us/library/ms162169.aspx). — hardmath, Jul 07 '12 at 13:31

score 1 · Accepted Answer · answered Jul 07 '12 at 13:55

As referenced in the comment from hardmath, Microsoft no longer distributes SQL-DMO libraries with SQL Server 2008 in favor of SQL SMO. Even so, you can still install SQL-DMO via 'Microsoft SQL Server 2005 Backward Compatibility Components' (you can find download links for that package on this page).

Here is an alternate way to enable SQL Server logins that does not use SQL-DMO:

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

I suspect that the above code will not work on versions of SQL Server prior to SQL Server 2008.

Nice, you've packed several useful tidbits in one answer! – hardmath Jul 07 '12 at 21:06 — hardmath, Jul 07 '12 at 21:06

SQL Query Error: SQLDMO

1 Answers1