Re: Import Logins from text or spread***

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

Date: Sat, 17 Jul 2004 09:02:05 -0500

Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.

set @SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
' 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@drew.edu> wrote in message
news:%23okGIu7aEHA.3892@TK2MSFTNGP10.phx.gbl...
> Possibly less prone to error is:
>
> CREATE PROCEDURE Process (
>   @SomeLogin sysname,
>   @SomePassword nvarchar(200) collate Latin1_General_CS_AS,
>   @SomeDatabase sysname
> ) as
>
> DECLARE @SQL nvarchar(4000)
> set @SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
>
> select
>   replace(replace(replace(replace(@sql,
>     '$L$',quotename(Login,'''')),
>     '$P$',quotename(Password,'''')),
>     '$D$',quotename(Database)),
>     '$R$',role)
> from openrowset ...
>
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
>
> Creating a 4th column in Excel is also a solution:
>
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
>
> Steve Kass
> Drew University
>
>
> Dan Guzman wrote:
>
> >>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$]')
> >
> >
> >