Solved

Can ALTER statement be used within a cursor?

Posted on 2008-06-19
6
1,105 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
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
Comment Utility
There he goes again....geez.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now