Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1155
  • Last Modified:

Can ALTER statement be used within a cursor?

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
CuseMMA
Asked:
CuseMMA
2 Solutions
 
Bill BachPresidentCommented:
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
 
chapmandewCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
chapmandewCommented:
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
 
CuseMMAAuthor Commented:
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
 
chapmandewCommented:
There he goes again....geez.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now