Solved

Can ALTER statement be used within a cursor?

Posted on 2008-06-19
6
1,121 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 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