sql-serversyntax-errorsql-server-2014sql-authentication

What is the proper syntax of "GO" in MSSQL?


I'm attempting to run a script that adds a login to MSSQL it includes two GO commands, both of which yield the error: Msg 102 ... Incorrect syntax near 'GO'. The following example code runs on a database named MyDB

DECLARE @currentUsername varchar(30);
DECLARE @password varchar(10);
SET @currentUsername = 'thisisatest';
SET @password = 'mypassword13';

DECLARE @addUser varchar(1000);
SET @addUser = '
    CREATE LOGIN ' + @currentUsername + '
    WITH PASSWORD = ''' + @password + ''',
    DEFAULT_DATABASE = [MyDB]
    GO
    USE MyDB
    CREATE USER ' + @currentUsername + ' FOR LOGIN ' + @currentUsername + '
    EXEC sp_addrolemember ''db_datareader'', ''' + @currentUsername + '''
    EXEC sp_addrolemember ''db_datawriter'', ''' + @currentUsername + '''
    GO
';
PRINT @addUser;
EXEC (@addUser);

Solution

  • GO isn't a regular SQL statement. From MSDN:

    GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

    Try breaking it up into multiple calls like this:

    DECLARE @currentUsername varchar(30);
    DECLARE @password varchar(10);
    SET @currentUsername = 'thisisatest';
    SET @password = 'mypassword13';
    
    DECLARE @addUser varchar(1000);
    SET @addUser = '
        CREATE LOGIN ' + @currentUsername + '
        WITH PASSWORD = ''' + @password + ''',
        DEFAULT_DATABASE = [MyDB]
    ';
    PRINT @addUser;
    EXEC (@addUser);
    
    SET @addUser = '
        USE MyDB
        CREATE USER ' + @currentUsername + ' FOR LOGIN ' + @currentUsername + '
        EXEC sp_addrolemember ''db_datareader'', ''' + @currentUsername + '''
        EXEC sp_addrolemember ''db_datawriter'', ''' + @currentUsername + '''
    ';
    PRINT @addUser;
    EXEC (@addUser);