Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? 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> 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 spreadsheet 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
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet 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
> spreadsheet? 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|||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 spreadsheet 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
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet 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$]')
>
>|||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 spreadsheet 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
> >spreadsheet. You can then copy/paste the results into a Query Analyzer
> >window and execute. The spreadsheet 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$]')
> >
> >
> >