Solved

Can ALTER statement be used within a cursor?

Posted on 2008-06-19
6
1,112 Views
Last Modified: 2010-04-21
My question is can the ALTER statement be used within a cursor? My situation is I have a database I just transferred over to a new server. I restored the database but now I have over 300 orphaned local SQL accounts. I used SSIS to transfer over the logins from the old server so I wouldn't have to worry about the SID issue. But now I have over 300 local accounts that are disabled! Rather than going to every one manually and enabling them I wanted to script this. I haven't worked much with cursors so I'm in dark territory here. I figured I would use a DMV to give me a list of all the users on the server. From that query I wanted to take each user name and pass it as a variable to an ALTER statement that would enable the account. I've written the cursor below but it won't work. Notice how I commented out the PRINT command? I used that line to see if the cursor was working correctly and sure enough it returns all my names as expected but when I try to pass the same variable to the ALTER statement it shoots back an error "Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@lname'." Any help would be appreciated!
declare @lname as nvarchar(128);
 
DECLARE custom_cursor CURSOR fast_forward FOR
select name from sys.syslogins;
OPEN custom_cursor
FETCH NEXT FROM custom_cursor into @lname;
SET @lname = @lname
 
WHILE @@FETCH_STATUS = 0
BEGIN
ALTER LOGIN @lname ENABLE
--PRINT @lname
FETCH NEXT FROM custom_cursor into @lname;
 
end
 
close custom_cursor;
deallocate custom_cursor;

Open in new window

0
Comment
Question by:CuseMMA
6 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21822592
A few alternate solutions:

1) Try building a statement string and use the EXEC/EXECUTE statement to run it within your loop.
2) Run a simple SQL statement to build ALL of the ALTER statements (don't forget the trailing semicolon) and export them all to a file.  Run all of the statements in the file.  (I use this quite a bit, and it is especially helpful for one-time-only conversions.)
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 65 total points
ID: 21823019
this should do it:

declare @sql nvarchar(2000)
declare @lname as nvarchar(128);
 
DECLARE custom_cursor CURSOR fast_forward FOR
select name from sys.syslogins;
OPEN custom_cursor
FETCH NEXT FROM custom_cursor into @lname;
SET @lname = @lname
 
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'ALTER LOGIN ' + @lname + ' ENABLE'
execute sp_executesql @sql
--PRINT @lname
FETCH NEXT FROM custom_cursor into @lname;
 
end
 
close custom_cursor;
deallocate custom_cursor;
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 60 total points
ID: 21824648
Hey chapmandew - I am back, thought you could use a bit of competition :)  but might wait for the next time -seems you have it nailed already... except... what's with the set @lname=@lname ?


declare @sql nvarchar(2000)
declare @lname as nvarchar(128)
 
DECLARE custom_cursor CURSOR fast_forward FOR select name from sys.syslogins
OPEN custom_cursor
FETCH NEXT FROM custom_cursor into @lname
WHILE @@FETCH_STATUS = 0
BEGIN
   set @sql = 'ALTER LOGIN ' + @lname + ' ENABLE'
   print @sql
   exec (@sql)
   FETCH NEXT FROM custom_cursor into @lname
end

close custom_cursor;
deallocate custom_cursor;
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21824993
Good catch...I have no idea.  It was something I just copied from the author's post.  :)

Nice to have you back...missed ya.  :)
0
 

Author Closing Comment

by:CuseMMA
ID: 31468786
Thanks guys, got it working! I was close just needed to do the extra step with using the sp_executesql. Oh and for everyone's knowledge, that @lname=@lname was a screw up I forgot to take out!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21825034
There he goes again....geez.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question