John John - 1 month ago 12
SQL Question

SQL Query Error: SQLDMO

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

Answer

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.