Post reply

Import users from excel

  • November 30, 2010 at 11:50 am

    #243405

    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

  • December 1, 2010 at 4:15 am

    This was removed by the editor as SPAM

  • December 1, 2010 at 4:42 am

    #1257232

    Can you use BCP to import users sql logins?

  • December 1, 2010 at 4:46 am

    #1257235

    which table do I import sql logins into?

  • December 1, 2010 at 5:45 am

    This was removed by the editor as SPAM

  • December 1, 2010 at 6:03 am

    #1257276

    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

  • December 1, 2010 at 7:53 am

    This was removed by the editor as SPAM

  • December 1, 2010 at 9:17 am

    #1257458

    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

  • December 1, 2010 at 9:42 am

    #1257476

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 1, 2010 at 10:18 am

    #1257488

    Yes the spreadsheet has the passwords

  • December 1, 2010 at 12:33 pm

    #1257565

    david.griffiths 57552 (12/1/2010)

    Yes the spreadsheet has the passwords

    -- Set the UserName and Password columns to how they are specified in the spreadsheet.

    -- Set the path/name of the spreadsheet, and the name of the sheet.

    SELECT 'CREATE LOGIN ' + QUOTENAME(UserName) + ' WITH PASSWORD=N''' + [PassWord] + ''';'

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;IMEX=1;Database=C:\SQL\SpreadSheetName.xls', [Sheet1$])

    This generates the sql statements to create the logins. Note that you need the Ad Hoc Query Options set with sp_configure. This does not run the statements; copy them to a new query window and run from there.

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 1, 2010 at 11:21 pm

    This was removed by the editor as SPAM

  • December 1, 2010 at 11:54 pm

    This was removed by the editor as SPAM

  • December 3, 2010 at 5:26 am

    #1258319

    You could also generate the create scripts in Excel using the concatenate function. Then copy the formula down the column and cut and paste the commands from Excel to SSMS

Viewing 14 posts - 1 through 13 (of 13 total)

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