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);
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);