Click here to monitor SSC
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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5765 Visits: 7128
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

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

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

Group: General Forum Members
Points: 5765 Visits: 7128
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5765 Visits: 7128
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),
   .
   .
   .
   Wink



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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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