Creating Mutliple Logins

Question

Creating Mutliple Logins

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
April 24, 2015 at 11:34 am

#307340

I had a need to test something recently and wanted to create multiple logins. Other than using a batch like this:
CREATE LOGIN TestUser1 WITH PASSWORD = 'UserT3stUn0';
CREATE LOGIN TestUser2 WITH PASSWORD = 'UserT3stD0s';
CREATE LOGIN TestUser3 WITH PASSWORD = 'UserT3stTr#s';
CREATE LOGIN TestUser4 WITH PASSWORD = 'UserT3stQuatr0';
CREATE LOGIN TestUser5 WITH PASSWORD = 'UserT3stCinc0';
Are there any suggestions for how to do this? Since it's security, I'm guessing any need for this would be using an Excel sheet or flat file with names/pwds and driving a CREATE LOGIN batch or PoSh from that.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

Yeahhhh.... PoSH... NOT! 😉

If they're just test users that will go away right after you're done testing, then something like the following might do.

DECLARE @SQL VARCHAR(MAX)

,@Logins SMALLINT = 1435

;

SELECT TOP (@Logins)

@SQL = ISNULL(@SQL,'')

+ REPLACE(REPLACE('

CREATE LOGIN TestUser<<Suffix>> WITH PASSWORD = "UserT3$t<<Suffix>>";'

,'"','')

,'<<Suffix>>',RIGHT(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+10000,4))

FROM sys.all_columns

;

PRINT @SQL

;

--EXEC (SQL)

;

If it needs something more sophisticated, post back with some details.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

Yeah, that's probably the best type of solution, Jeff. Didn't think about tally tabling that, but it's a good use with dynamic SQL for a limited number of items, along with adding users.

Thanks

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

Thanks for the feedback, Steve.

As for the "user" part of this, we also used similar code to create the code at the DOS level to create the AD users and add them to a group for the "load testing" that we do once a quarter. We also generated similar code to undo all of it.

And, of course (you know me), we executed all of it through calls to xp_CmdShell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Jeff Moden (4/24/2015)
Yeahhhh.... PoSH... NOT! 😉

Surely you meant "Not posh enough":-D

😎

normally do these things on linux using sqsh[/url].

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

Eirikur Eiriksson (4/26/2015)
Jeff Moden (4/24/2015)
Yeahhhh.... PoSH... NOT! 😉
Surely you meant "Not posh enough":-D
😎
normally do these things on linux using sqsh[/url].

Don't call me "Shirley". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

Jeff Moden (4/26/2015)
Eirikur Eiriksson (4/26/2015)
Jeff Moden (4/24/2015)
Yeahhhh.... PoSH... NOT! 😉
Surely you meant "Not posh enough":-D
😎
normally do these things on linux using sqsh[/url].
Don't call me "Shirley". 😉

ROFL, can I call you "Certainly"?

😎