Import users from excel

  • 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

  • This was removed by the editor as SPAM

  • Can you use BCP to import users sql logins?

  • which table do I import sql logins into?

  • This was removed by the editor as SPAM

  • 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

  • This was removed by the editor as SPAM

  • 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

  • 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

  • Yes the spreadsheet has the passwords

  • 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

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • 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