Capture Logins Script from KB 878449 generates Invalid object name error
This week I had an interesting case from a partner trying to use the Capture Logins script from Knowledge Base (KB) article 878449.
When they ran the script on their SQL Server 2012 system they received the follow error:
Invalid object name 'master..sysxlogins'.
Looking at the source code for the script where the error occurs, I could see that the code has two branches. For the newer SQL Server versions it is meant to call the temporary stored procedure seeMigrateSQLLogins, otherwise it should call the temporary stored procedure sp_help_revlogin.
So why was it running the wrong branch of the code?
The branch executed is decided by an if statement which checks the version number information returned from the @@version system variable. When I tested the code using print statements to display what version number the if statement was comparing against on my SQL Server 2012 system, it displayed "P1) " when the code was expecting "11.0".
The problem occurs because the @@version for SQL 2012 SP1 shifts the version number six characters to the right, causing the version number check to fail and the wrong branch of the code to execute.
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
The original code below cannot handle this change. In fact it has to be changed for every version of SQL Server as the layout of the information return by the @@version command might have changed.
Original script Excerpt
declare @version2005 char(5)
declare @version2008 char(5)
declare @version2008R2 char(5)
--Get the current version of SQL Server running
select @version2005 = substring(@@version,29,4)
select @version2008 = substring(@@version,35,4)
select @version2008R2 = substring(@@version,38,4)
if (@version2005 in ('9.00','11.0') or @version2008 = '10.0' or @version2008R2 = '10.5')
We could just keep using the same method and extend it to work with SQL Server 2012 SP1 (and tidy up the SQL Server 2012 handling as well). However, this will still need to be updated for each new SQL Server version, see below:
Modified Script Excerpt
declare @version2005 char(5)
declare @version2008 char(5)
declare @version2008R2 char(5)
declare @version2012 char(5)
declare @version2012SP1 char(5)
--Get the current version of SQL Server running
select @version2005 = substring(@@version,29,4)
select @version2008 = substring(@@version,35,4)
select @version2008R2 = substring(@@version,38,4)
select @version2012 = substring(@@version,29,4)
select @version2012SP1 = substring(@@version,35,4)
if (@version2005 = '9.00' or @version2008 = '10.0' or @version2008R2 = '10.5' or @version2012 = '11.0' or @version2012SP1 = '11.0')
A better method is to make the code smart and version independent. We can achieve this by using the patindex() function looking for the string #.# (Number, Full stop (period), Number) in the version details and then starting one character before the location returned (to capture two digit major version numbers) and grab the following 4 characters using substring(). By trimming off the left hand spaces using ltrim() we can remove the space at the beginning when the major version number is only a single digit (ie. version 9.0). Finally using cast() to change the data type allows for a numeric comparison of the version to check if it is version 9.0 or greater.
Below is the version proof code which will work for any version and does not need changes unless the method used needs to change:
Final Script Excerpt
if cast(ltrim(substring(@@version, patindex('%[0-9].[0-9]%', @@version)-1,4)) as decimal(4,2)) >= 9.0
Try the following on your system. This will display the major and minor version numbers for your SQL Server.
print ltrim(substring(@@version,patindex('%[0-9].[0-9]%', @@version)-1,4))
The archive attached to the bottom of this article contains three script files: the original script (KB878449_Capture_Logins_Original.sql), the modified version (KB878449_Capture_Logins_Modified.sql) and then the final version (to replace the original: KB878449_Capture_Logins.sql) using the patindex() approach.
Hope you find this useful.
David
PS: I have requested that the script file associated with the KB article be updated with the new version I created.