InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 16:46:52 GMT20http://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspx[quote][b]Bhuvnesh (1/9/2013)[/b][hr][quote][b]ScottPletcher (1/9/2013)[/b][hr]the cursor should be optimized with FAST_FORWARD:[/quote] Any article reference for this ?[/quote]Books Online.Thu, 10 Jan 2013 08:29:00 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspx[quote][b]ScottPletcher (1/9/2013)[/b][hr]the cursor should be optimized with FAST_FORWARD:[/quote] Any article reference for this ?Wed, 09 Jan 2013 23:36:19 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspxI, too, don't have a problem with a cursor [i]per se[/i] here; it could be done w/o one, but it's not a big deal for such a limited number of rows.You should get away from "sysusers", which is also obsolete, but again that's not a killer issue either.But, the cursor should be optimized with FAST_FORWARD:[code="sql"]...declare updateServerroles cursor [b]fast_foward[/b] for SELECT name FROM sysusers where name = 'TEST\user'...[/code][b]Edit[/b]: Corrected typo.Wed, 09 Jan 2013 15:18:15 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspx[quote][b]Bhuvnesh (1/9/2013)[/b][hr][quote][b]paul 77096 (1/9/2013)[/b][hr]Thats Brilliant, it worked like a dream.[/quote] in future try to avoid cursors instead use loop or set based approach. why ???? google it :-)[/quote]Cursors VS Loops is an argument that will never be won. a cursor can be faster than a while loop if written correctly. This is one place where a loop or cursor is appropriate as each user needs to be run and is by its nature an RBAR process.Wed, 09 Jan 2013 14:26:45 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspxHi, very true!Cursors aren’t all bad. In fact, they can make some database problems a lot easier to solve. But take a step back and really examine the problem. There is a chance that you can use a more efficient set-based approach to solve your problem.Wed, 09 Jan 2013 05:43:25 GMTatapia_http://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspx[quote][b]paul 77096 (1/9/2013)[/b][hr]Thats Brilliant, it worked like a dream.[/quote] in future try to avoid cursors instead use loop or set based approach. why ???? google it :-)Wed, 09 Jan 2013 04:44:15 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspxThats Brilliant, it worked like a dream. ThanksPaulWed, 09 Jan 2013 04:31:04 GMTpaul 77096http://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspxtry this in your cursor code [code="sql"]DECLARE @SQL NVARCHAR(4000);While begin SET NOCOUNT ON SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''12345'', DEFAULT_DATABASE=[dbname], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON'; EXECUTE(@SQL); exec sp_addrolemember db_datareader, @username ; exec sp_addrolemember db_datawriter, @username ;end [/code]Wed, 09 Jan 2013 04:24:35 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1404651-392-1.aspxHi I have 100 or so users in a database but I need them all to have a server login alsoI was thinking of doing this in a cursor and have come up with [code="plain"]Declare @username varchar(50);declare updateServerroles cursor forSELECT name FROM sysusers where name = 'TEST\user'open updateServerrolesfetch next from updateServerrolesinto @usernameWhile @@FETCH_STATUS = 0beginCREATE LOGIN @username FROM WINDOWS WITH DEFAULT_DATABASE=[***********], DEFAULT_LANGUAGE=[us_english]exec sp_addrolemember db_datareader, @username exec sp_addrolemember db_datawriter, @username end close updateServerrolesDEALLOCATE updateServerroles[/code]but the 'create login' will not work. Please can someone give me some advisethanks in advance PaulWed, 09 Jan 2013 03:35:49 GMTpaul 77096