SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import users from excel


Import users from excel

Author
Message
david.griffiths-989338
david.griffiths-989338
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
I have 400 usersnames that I need to import from a spreadsheet.

I need to create sql logins for each user.

With username, password and which database they are able to vieiw

Then set the windows login to enable until I remove there windows login and chnage to database.

This needs to be done becuase I am migrating to a different domain
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10901 Visits: 7319
you can either import the data into SQL using BCP or SSIS, alternatively access the excel spreadsheet using the OPENROWSET function:
http://msdn.microsoft.com/en-us/library/ms190312.aspx

then run this list through a cursor and create login
http://msdn.microsoft.com/en-us/library/ms189751(v=SQL.100).aspx

and add to a database role
http://msdn.microsoft.com/en-us/library/ms189121(v=SQL.100).aspx
or grant access directly to a database
http://msdn.microsoft.com/en-us/library/aa337545(v=SQL.100).aspx

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
david.griffiths 57552
david.griffiths 57552
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 12
Can you use BCP to import users sql logins?
david.griffiths 57552
david.griffiths 57552
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 12
which table do I import sql logins into?
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10901 Visits: 7319
for BCP, you would create a temp table (#tempTable) and insert the result of the BCP into this table.

then iterate through this result set and execute the create login etc scripts

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
david.griffiths 57552
david.griffiths 57552
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 12
Sorry being new to sql script

I have looked at the examples but it do not show how to import from another database.

After i created the tempory one
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10901 Visits: 7319
An example of how to do this:
First create a temp table in your SQL Management Studio, containing all the columns in your spreadsheet:

CREATE TABLE #Staging
( UserId CHAR(15),
UserName VARCHAR(35),
.
.
.
)



Next, use BULK INSERT http://msdn.microsoft.com/en-us/library/ms188365(v=SQL.100).aspx to insert the data into this temp table

BULK INSERT #Staging
FROM '\\Share\Folder\FileName.xls'
WITH(
BATCHSIZE = 15000,
CHECK_CONSTRAINTS,
DATAFILETYPE = 'native',
ROWS_PER_BATCH = 15000,
,
,
)



Then declare a cursor to iterate through the imported list to create the logins and allocate to a given database

DECLARE @UserID CHAR(15), @UserName VARCHAR(35), ......
DECLARE Users CURSOR LOCAL FAST_FORWARD FOR
SELECT UserId, UserName .....
FROM #Staging
OPEN Users
FETCH NEXT FROM Users INTO @UserID, @UserName, .....
WHILE @@FETCH_STATUS = 0 BEGIN

--Create login
DECLARE @Login NVARCHAR(100) =
'CREATE LOGIN '+@UserID+'
FROM WINDOWS;'
BEGIN TRY
EXEC sp_executesql @Login
END TRY
BEGIN CATCH
RAISERROR('There was an error creating login for user %s',16,1,@UserID)
END CATCH

--Grant Acccess to DB
SELECT @Login = 'USE GivenDatabase
CREATE USER '++@UserID+'
FOR LOGIN '+@UserID
BEGIN TRY
EXEC sp_executesql @Login
END TRY
BEGIN CATCH
RAISERROR('There was an error linking login for user %s to database GivenDatabase',16,1,@UserID)
END CATCH

FETCH NEXT FROM Users INTO @UserID, @UserName, .....

END
CLOSE Users
DEALLOCATE Users
DROP TABLE #Staging



Does this help?

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
david.griffiths 57552
david.griffiths 57552
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 12
Thanks I will try this.

Just one point I am creating database logins not windows.

In script can I just remove the Create Logins from Windows, will this just create the user as per the table import
WayneS
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12129 Visits: 10601
Just curious... when creating sql logins, you need to specify a password. Does the Excel spreadsheet also have that specified, or how are you implementing that?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

david.griffiths 57552
david.griffiths 57552
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 12
Yes the spreadsheet has the passwords
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search