Syntax check for creating logins in different SQL versions

Question

We are trying to write a script to do the following task:

Create a new login and grant datareader to the master database on all SQL Server 2012 and 2014.

The script we have did what we want but for some reason, SSMS is complaining about the syntax saying the user already exists but it doesn't exist.

    use master
    go
    IF EXISTS  
    (select * from sys.syslogins
    where loginname =  'login_name') 
    (select 1 from sys.syslogins)
    else

    if
    (select  left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '12'
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE USER [login_name] FOR LOGIN [login_name]
    ALTER ROLE [db_datareader] ADD MEMBER [login_name]

    if 
    (select  left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '11' 
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE USER [login_name] FOR LOGIN [login_name]
    ALTER ROLE [db_datareader] ADD MEMBER [login_name]

Answer 1

The trouble here is that the script evaluates the IF statement, and then the logic applies to the first statement afterwards. To handle multiple statement you need to put them within a BEGIN...END block.

Something like this should work for you

  USE master
    GO
    IF NOT EXISTS ( SELECT  *
                    FROM    sys.syslogins
                    WHERE   loginname = 'login_name' )
        BEGIN
            IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),
                             2)
               ) = '12'
                BEGIN
                    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
                    CREATE USER [login_name] FOR LOGIN [login_name];
                    ALTER ROLE [db_datareader] ADD MEMBER [login_name];
                END
            ELSE
                IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),
                                 2)
                   ) = '11'
                    BEGIN
                        CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
                        CREATE USER [login_name] FOR LOGIN [login_name];
                        ALTER ROLE [db_datareader] ADD MEMBER [login_name];
                    END
        END
    ELSE
        BEGIN
            SELECT  1;
        END

Answer 2

Hope this helps

 USE master;
        GO
 IF EXISTS ( SELECT *
             FROM   sys.syslogins
             WHERE  loginname = 'login_name' )
    ( SELECT    1
      FROM      sys.syslogins
    );
 ELSE
    IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2)
       ) = '12'
        CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
 CREATE USER [login_name] FOR LOGIN BEGIN [login_name];
 EXECUTE sp_addrolemember db_datareader, 'login_name';

 IF ( SELECT    LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2)
    ) = '11'
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
 CREATE USER [login_name] FOR LOGIN [login_name];
 EXECUTE sp_addrolemember db_datareader, 'login_name';