Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Import users from excel Expand / Collapse
Author
Message
Posted Tuesday, November 30, 2010 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 01, 2010 5:10 AM
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

Post #1028161
Posted Wednesday, December 01, 2010 4:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 3,679, Visits: 4,814
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”
Post #1028507
Posted Wednesday, December 01, 2010 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 03, 2010 6:54 AM
Points: 6, Visits: 12
Can you use BCP to import users sql logins?
Post #1028512
Posted Wednesday, December 01, 2010 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 03, 2010 6:54 AM
Points: 6, Visits: 12
which table do I import sql logins into?
Post #1028515
Posted Wednesday, December 01, 2010 5:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 3,679, Visits: 4,814
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”
Post #1028552
Posted Wednesday, December 01, 2010 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 03, 2010 6:54 AM
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
Post #1028561
Posted Wednesday, December 01, 2010 7:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 3,679, Visits: 4,814
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”
Post #1028669
Posted Wednesday, December 01, 2010 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 03, 2010 6:54 AM
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
Post #1028764
Posted Wednesday, December 01, 2010 9:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:06 PM
Points: 6,543, Visits: 8,751
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
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
Post #1028786
Posted Wednesday, December 01, 2010 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 03, 2010 6:54 AM
Points: 6, Visits: 12
Yes the spreadsheet has the passwords
Post #1028801
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse