Text file as script input

Question

Text file as script input

lanky_doodle

SSCrazy

Points: 2378
More actions
March 26, 2012 at 4:17 am

#270729

Hiya,
I have this script;
USE [master]
CREATE LOGIN [DOMAIN\User.Name] FROM WINDOWS WITH DEFAULT_DATABASE = DBName, DEFAULT_LANGUAGE = British;
GO
USE [DBName]
GO
CREATE USER [User.Name] FOR LOGIN [DOMAIN\User.Name]
GO
USE [DBName]
GO
EXEC sp_addrolemember N'db_datareader', N'User.Name'
GO
USE [DBName]
GO
EXEC sp_addrolemember N'db_datawriter', N'User.Name'
GO
If I have hundred's of users, which I do have, can I use an exported list from our HR package to use as input and lopp the above through it? I would only need a file with each username on seperate lines.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

since no one has posted yet and im not seeing any other way than a loop (i am new at this) you can try this: load the file using BCP (or other method to get the data into a temp table) and run something like the following:

DECLARE @UserName

DECLARE @CMD VARCHAR(MAX)

DECLARE NameCursor CURSOR FOR

SELECT UserName FROM #TempUserNameTable

ORDER BY UserName;

OPEN NameCursor;

FETCH NEXT FROM NameCursor

INTO @UserName

WHILE @@FETCH_STATUS = 0

BEGIN

USE [master]

SET @CMD = 'CREATE LOGIN DOMAIN\' + QUOTENAME(@UserName) + 'FROM WINDOWS WITH DEFAULT_DATABASE = DBName, DEFAULT_LANGUAGE = British'

EXEC (@CMD)

USE [DBName]

SET @CMD = 'CREATE USER ' + QUOTENAME(@UserName) + ' FOR LOGIN DOMAIN\' + QUOTENAME(@UserName)

EXEC (@CMD)

EXEC sp_addrolemember N'db_datareader', QUOTENAME(@UserName)

EXEC sp_addrolemember N'db_datawriter', QUOTENAME(@UserName)

FETCH NEXT FROM NameCursor

INTO @UserName

END

Close NameCursor

DEALLOCATE NameCursor

all the caveats with dynamic sql do apply so this may not be the best method.

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I would not do this. I would look at using Windows groups and assigning appropriate permissions to each of the groups. This leaves the administration of users with the AD Admin, and simplifies the administration of SQL Server. As new users are created, they are added to the appropriate group. As people leave, they are deleted. If they transfer between departments, it is a simple change to which group(s) they are in.

lanky_doodle SSCrazy Points: 2378 More actions · Answer 3

I agree 100% Lynn, however it's a 3rd party we're talking about here and its permissions are granted per user. The app also maintains it own 'name' list (as in firsname, lastname, initials, email address etc) and each login ID is mapped to its relevant name.

So it has to be per user!