Re: Import Logins from text or spread***

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/17/04 

Date: Fri, 16 Jul 2004 20:44:12 -0500

> 1. add login
> 2. set psw to generic value
> 3. set default database

4. add user to database
5. add them to user defined role

The required script template:

EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'

Let's assume your Excel spread*** has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spread***. You can then copy/paste the results into a Query Analyzer
window and execute. The spread*** file needs be accessible by the SQL
Server service.

SELECT
'EXEC sp_addlogin ''' +
    Login +
    ''', ''SomePassword'', ''' +
    DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
    Login + '''
EXEC sp_addrolemember ''' +
    Role +
    ''', ''' +
    Login +
    ''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;DATABASE=c:\temp\Logins.xls',
    'Select * from [Sheet1$]') 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@phx.gbl...
> Is there a way to create logins from a text file or
> spread***?  I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
>
> Thanks,
> Brian