Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

creating SQL login with cursor Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 9:51 AM
Points: 2, Visits: 6
Hi I have 100 or so users in a database but I need them all to have a server login also

I was thinking of doing this in a cursor and have come up with

Declare @username varchar(50);

declare updateServerroles cursor for
SELECT name FROM sysusers where name = 'TEST\user'


open updateServerroles

fetch next from updateServerroles
into @username

While @@FETCH_STATUS = 0
begin

CREATE 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 updateServerroles
DEALLOCATE updateServerroles



but the 'create login' will not work. Please can someone give me some advise

thanks in advance

Paul
Post #1404651
Posted Wednesday, January 9, 2013 4:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
try this in your cursor code

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




-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1404669
Posted Wednesday, January 9, 2013 4:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 9:51 AM
Points: 2, Visits: 6
Thats Brilliant, it worked like a dream. Thanks

Paul
Post #1404673
Posted Wednesday, January 9, 2013 4:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
paul 77096 (1/9/2013)
Thats Brilliant, it worked like a dream.
in future try to avoid cursors instead use loop or set based approach. why ???? google it


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1404680
Posted Wednesday, January 9, 2013 5:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 10, 2013 5:15 AM
Points: 339, Visits: 71
Hi, 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.



Ing. Aneurys Tapia González
DBA + BI Architect
Dominican Republic
atapia@outlook.com | admin@atapia.net
http://sqlservermasbi.wordpress.com
http://atapia.net

Post #1404714
Posted Wednesday, January 9, 2013 2:26 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
Bhuvnesh (1/9/2013)
paul 77096 (1/9/2013)
Thats Brilliant, it worked like a dream.
in future try to avoid cursors instead use loop or set based approach. why ???? google it


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.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1405012
Posted Wednesday, January 9, 2013 3:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 2,102, Visits: 3,164
I, too, don't have a problem with a cursor per se 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:

...
declare updateServerroles cursor fast_foward for SELECT name FROM sysusers where name = 'TEST\user'
...

Edit: Corrected typo.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1405042
Posted Wednesday, January 9, 2013 11:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
ScottPletcher (1/9/2013)
the cursor should be optimized with FAST_FORWARD:
Any article reference for this ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1405200
Posted Thursday, January 10, 2013 8:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 2,102, Visits: 3,164
Bhuvnesh (1/9/2013)
ScottPletcher (1/9/2013)
the cursor should be optimized with FAST_FORWARD:
Any article reference for this ?



Books Online.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1405493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse