Advice on applying variables as part of a statement utilising a cursor / while loop

Question

Hello all

Please can someone advise on the following. I have a script to update user permissions, which I've been attempting to make dynamic (so a list of username names can be looped through, to apply db permissions via stored procedure).

I've not had a great deal of exposure to tsql / cursors, but I'm getting several incorrect syntax errors with the following: -

while @@fetch_status = 0 begin

CREATE LOGIN '[lg\' + @UserLogin + ']' FROM WINDOWS WITH DEFAULT_DATABASE = [master]

USE uhxx
EXEC sp_grantdbaccess '''lg\' + @UserLogin + '''', '''' + Lower (@UserLogin) + ''''  
GO
USE wfxx
GO
EXEC sp_grantdbaccess '''lg\' + @UserLogin + '''', '''' + Lower (@UserLogin) + ''''  
GO
ALTER USER Lower (@UserLogin) WITH DEFAULT_SCHEMA = Lower (@UserLogin);
GO
EXEC sp_addrolemember 'synchronization', '''' + Lower (@UserLogin) + ''''
GO

fetch next from UserLogins into @UserLogin
end

Would appreciate any advice on this. The errors mainly relate to the concatenations used to accomodate the variables: -

  
Msg 102, Level 15, State 1, Line 12

Incorrect syntax near '[lg\'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '+'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '+'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '+'.

Msg 137, Level 15, State 2, Line 3

Must declare the scalar variable "@UserLogin".

Regards

Michael

Answer 1

First you need to form the string and then use exec (@str) Sample below

declare @UserLogin varchar(100)
set @UserLogin = 'Balmukund'
declare @loginStr varchar(100)
Set @loginStr = 'CREATE LOGIN ' + '[MyDomain\' + @UserLogin + ']' +' FROM WINDOWS WITH DEFAULT_DATABASE = [master]'
exec (@loginStr)

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 2

Thanks for the prompt response Balmukund. I'm still getting errors regarding the declaration of @StringTest2, which I've created to test the changes you've suggested.

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@StringTest2".

I have declared this variable at the beggining of the statement, so unsure why I'm receiving this error.

Answer 3

You need to show us the new script if you want help debugging it.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Set @loginStr = 'CREATE LOGIN ' + '[MyDomain\' + @UserLogin + ']' +' FROM WINDOWS WITH DEFAULT_DATABASE = [master]'

Permit me to point out that this:

   '[MyDomain\' + @UserLogin + ']'

should be:

   quotename('MyDomain\' + @UserLogin)

While it is highly unlikely that @UserLogin would include a right bracket, there is no reason to attempt to handle this situation. Not the least if @UserLogin could be populated from a source which has been filled up by a malicous user who is engaging in SQL injection.

Another problem in the original post is that there are a lots of GO in the script. GO is not an SQL command. GO is an instruction to the query tool to cut the script here and send this part to SQL Server.

There are also a number of USE commands, this will mess things up and make things more difficult to understand. Rather use the fact that when you say:

EXEC somedb..sp_systemproc

sp_systemproc will execute in the contect ot that database.

Here is an improvment of the cursor loop:

  DECLARE cur FOR ....

  OPEN cur

  WHILE 1 = 1
  BEGIN
     FETCH cur INTO @UserLogin
     IF @@fetch_status <> 0 BREAK

     SELECT @domainuser = quotename('MyDomain\' + @UserLogin)
     SELECT @lc_UserLogin = quotename(lower(@UserLogin))
     SELECT @addlogin =  'CREATE LOGIN ' + @domainuser +
                   ' FROM WINDOWS WITH DEFAULT_DATABASE = [master]'
     SELECT @adduser = 'CREATE USER ' + @lc_UserLogin +
                       'FROM ' + @domainuser

     EXEC sp_executesql @addlogin
     EXEC uhxx..sp_executesql @adduser
     EXEC wfxx..sp_executesql @adduser

     SELECT @sql = 'CREATE SCHEMA ' + @lc_UserLogin
     EXEC wfxxx..sp_executesql @sql

     SELECT @sql = 'ALTER USER ' + @lc_UserLogin ' +
                   'WITH DEFAULT_SCHEMA = ' + @lc_UserLogin
     EXEC wfxxx..sp_executesql @sql

     EXEC wfxx..sp_addrolememeber 'synchronization', @lc_UserLogin
 END

 DEALLOCATE cur

Note that the argument to sp_executesql must be nvarchar.

---

Erland Sommarskog, SQL Server MVP, [e-mail]