sp_help_revlogin – ‘LOGINPROPERTY’ is not a recognized function name

The Microsoft Knowledge Base Article 246133 – How to transfer logins and passwords between instances of SQL Server details how to create the Stored Procedure sp_help_revlogin. This stored procedure is used to transfer logins from one SQL Server instance to another. However the latest revision of this article has been modified for SQL Server 2005 and is unable to be run on SQL Sever 2000 without modification.

When the code in the article is run to create the sp_help_revlogin Stored Procedure on SQL Server 2000 the following error occurs:

Server: Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 52
‘LOGINPROPERTY’ is not a recognized function name.

Below is a copy of the code for sp_help_revlogin Stored Procedure that will execute on SQL server 2000.  This code is from an archived version of the Knowledge Base Article.

~~~

IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘
+ CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
PRINT ‘DECLARE @pwd sysname’
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN — NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN — NT login is denied access
SET @tmpstr = ‘EXEC master..sp_denylogin ”’ + @name + ””
PRINT @tmpstr
END
ELSE BEGIN — NT login has access
SET @tmpstr = ‘EXEC master..sp_grantlogin ”’ + @name + ””
PRINT @tmpstr
END
END
ELSE BEGIN — SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN — Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = ‘SET @pwd = CONVERT (varchar(256), ‘ + @txtpwd + ‘)’
ELSE
SET @tmpstr = ‘SET @pwd = CONVERT (varbinary(256), ‘ + @txtpwd + ‘)’
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name
+ ”’, @pwd, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘
END
ELSE BEGIN
— Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name
+ ”’, NULL, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘
END
IF (@xstatus & 2048) = 2048
— login upgraded from 6.5
SET @tmpstr = @tmpstr + ”’skip_encryption_old”’
ELSE
SET @tmpstr = @tmpstr + ”’skip_encryption”’
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

~~~

DMO to Check if Database is Suspect

One of the emails I received whilst on leave was asking if it is possible to check if a database has been marked suspect using SQLDMO.  Below is a VBScript snippet of how the database status can be checked to determine if the database has been marked suspect or not:

~~~

Dim oSQLServer

Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)

oSQLServer.LoginSecure = True
oSQLServer.Connect “(local)”

If oSQLServer.Databases(“foo”).Status AND 256 = 256 Then
MsgbOx “The foo database is marked as suspect”
End If

oSQLServer.Disconnect
Set oSQLServer = Nothing

~~~

I’m Back…

After almost four weeks touring northern Italy with Jay Dee I have returned from leave refreshed and now knowing what my wife actually looks like.  During this trip we came up with some great ideas for WARDY IT, so watch this space over the next couple of months as we start to implement some of these ideas.