December 4, 2003 at 7:01 am
We are needing to load 250-300 users to a database. Is there any way to load the users and passwords, etc, from a spreadsheet or delimited text file? Does anyone have a script that will do this? I am in security, not development.
Ed
December 4, 2003 at 9:22 am
Check http://support.microsoft.com/default.aspx?scid=kb;en-us;246133&Product=sql
there you will find an sp that will script the creation of the logins with the same id and password.
but the result will be T-SQL code, wich you won't be able to import. You just execute it on the destination server.
December 4, 2003 at 1:25 pm
A clarification on our situation: we do not have these logins on an existing database, but need to load the users onto 4 databases as totally new users. We will have lofin information in text form from the remote site, either in an Excel spreadsheet of a formated WORD document. We really don't want to do them individually by hand.
Ed
December 4, 2003 at 1:45 pm
you will then need to code a sp_add_login and sp_adduser statements for each user. You should be able to easily build a text file T-SQL script from you raw data.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:qa.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 4, 2003 at 3:29 pm
1>Use excel or word to convert the login to match the SQL server login and user requirments, including login, password, default database, username etc.
2>Import it to a database as a normal table named as tb_login_user table
3>Create a cursor script to complete sp_addlogin and sp_adduser
4>Repeat it on other servers, or use DTS to transfer logins to the rest servers
December 5, 2003 at 5:38 am
I often get data from spreadsheets into a database using Excel's CONCATENATE function to construct SQL from the spreadsheet data, then copy/paste the result into Query Analyzer. For example, if you had your logins in column A, with passwords in column B, then in column C you enter:
=CONCATENATE("exec sp_addlogin @loginame = '", A1, "' , @password = '", B1, "'")
Copy this cell all the way down column C, the copy/paste column C into Query Analyzer and GO.
December 5, 2003 at 5:44 am
You could use the mail merge feature in MS Word. Just write your 'exec sp_addlogin' and 'exec sp_adduser' statements then merge it together with your formatted source file. Then, just run the script it creates in your target database.
exec sp_addlogin '<<LOGINNAME_FROM_TEXT_FILE>>', '<<PASSWORD_FROM_TEXT_FILE>>'
exec sp_adduser '<<LOGINNAME_FROM_TEXT_FILE>>'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply