Incorrect syntax near \ and GO with user creation loop

Question

I'm hitting a syntax issue with my antislash and dynamic sql exec and was wondering what the easiest way is to correct this loop to denote the \ and whatever other special chars are appearing in my dynamic sql in the below user login creation loop:

DECLARE @Domain AS VARCHAR(50) = 'WidgetFactory\'
DECLARE @BasicNameList as VARCHAR(MAX) = 'user1,user2,user3'
DECLARE @NAME AS VARCHAR(50)
DECLARE @Query AS NVARCHAR(MAX)

WHILE LEN(@BasicNameList) > 0
BEGIN
    SET @NAME = LEFT(@BasicNameList, charindex(',', @BasicNameList+',')-1)

    SET @Query =   'If not Exists (select loginname from master.dbo.syslogins 
                        where name = ' + @Domain + @NAME + ' and dbname = ''master'')
                    Begin
                        USE [master]
                        GO
                        CREATE LOGIN [' + @Domain + @NAME + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
                        GO
                        ALTER SERVER ROLE [BasicDBA] ADD MEMBER [' + @Domain + @NAME + ']
                        GO
                        USE [DB1]
                        GO
                        CREATE USER [' + @Domain + @NAME + '] FOR LOGIN [' + @Domain + @NAME + ']
                        GO
                        USE [DB1]
                        GO
                        ALTER ROLE [db_datareader] ADD MEMBER [' + @Domain + @NAME + ']
                        GO
                        USE [DB2]
                        GO
                        CREATE USER [' + @Domain + @NAME + '] FOR LOGIN [' + @Domain + @NAME + ']
                        GO
                        USE [DB2]
                        GO
                        ALTER ROLE [db_datareader] ADD MEMBER [' + @Domain + @NAME + ']
                        GO
                        USE [master]
                        GO
                        CREATE USER [' + @Domain + @NAME + '] FOR LOGIN [' + @Domain + @NAME + ']
                        GO
                        USE [master]
                        GO
                        ALTER ROLE [db_datareader] ADD MEMBER [' + @Domain + @NAME + ']
                        GO                      
                    END'

    EXEC(@Query)

  SET @BasicNameList = STUFF(@BasicNameList, 1, CHARINDEX(',', @BasicNameList+','), '')
END

Johann · Answer 1 · 2019-02-15T15:06:36.417

0

The problem is not the backslash. The problem seems to be that GO is not valid in dynamic sql. Maybe you can change your script to execute each statement in an own transaction?

Problem with GO in T-SQL

edited Feb 15 '19 at 15:06

answered Feb 14 '19 at 06:46

Johann

349
2
9

the backslash is necessary. Per my request, is there a way to denote the \ so it can still be passed? Additionally, that is not the only issue. There's also something happening in the @Query dynamic sql assignment as well that would also need to be resolved. – Kevin Henzel Feb 14 '19 at 18:05
Sorry, I was to quick and have better invested more time before answering your question. I update my answer. – Johann Feb 15 '19 at 14:52

Incorrect syntax near \ and GO with user creation loop

1 Answers1