creating SQL login with cursor

Question

Post reply

creating SQL login with cursor

paul 77096

SSC Veteran

Points: 278
More actions
January 9, 2013 at 3:35 am

#393907

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

paul 77096 SSC Veteran Points: 278 More actions · Answer 2

Thats Brilliant, it worked like a dream. Thanks

Paul

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

atapia_ SSC-Addicted Points: 487 More actions · Answer 4

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

CapnHector SSCoach Points: 16143 More actions · Answer 5

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[/url] 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[/url]

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

ScottPletcher SSC Guru Points: 101118 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

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;-)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".