Capture Logins Script from KB 878449 generates Invalid object name error

David Meego - Click for blog homepageThis 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.


PS: I have requested that the script file associated with the KB article be updated with the new version I created.

Comments (13)

  1. David Musgrave says:

    Posting from Mariano Gomez, The Dynamics GP Blogster…/kb-article-878449-capture-login-script.html

  2. SheilaJR says:

    Thanks to both you and Mariano, this information will save me much time searching for a solution.  Thanks again for being such a great resource to the community.


  3. RS says:

    If you just want the numeric version for SQL, this command may work little better and it goes back to SQL 2000: SELECT SERVERPROPERTY('productversion')

  4. Chandan Gupta says:


    The new version file works excellent for me , Thanks a ton.


    Chandan Gupta

  5. Janeece Moreland says:

    Thank you!

  6. Daryle says:

    I was having this same issue with the original script and then tried yours. Now I get this error. Must declare the scalar variable “@version”. I am using SQL2014. Where can I declare the variable? Im not that good with these scripts, so might need simple steps to follow.


  7. This script works super fine for me but, why the new GP Server has to have the same name than the old one to the logins work? there is any way to change this?

    1. Hi Santiago

      Passwords in Dynamics GP are encrypted and that encryption includes the name of the SQL Server. If you change the SQL Server Name, the passwords are no longer valid.

      Please see the article below for more information:

      Please note that the Database Validation feature of GP Power Tools ( can recreate logins and passwords for you.


      1. Isabel says:

        I need your help, I have moved from 9 to GP2013, and I am getting the error with CleanUpFileLoginsBeforeLogin, The server has different names, but we are not ready to switch from test to production. How I can approach it. Please advise

        1. Hi Isabel

          If you want more control over how you recreate the logins, use GP Power Tools’ Database Validation feature.


  8. Steven Mayhue says:


    Thanks for the assist. Couldn’t get a good answer on the forums until searching with space between capture logins. Without the space you get the same stale non working post.

    Really helpful post and great explanation.

    1. Hi Steven

      Glad the article helped


Skip to main content