Re: Import Logins from text or spread***

From: Steve Kass (skass_at_drew.edu)
Date: 07/17/04

  • Next message: Umut Nazlica: "RE: Maint Plans" 
    Date: Sat, 17 Jul 2004 01:04:30 -0400

    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$]')
    >
    >
    >

  • Next message: Umut Nazlica: "RE: Maint Plans"