Re: Import Logins from text or spreadsheet

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

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

    Relevant Pages

    • Re: Full text catalog just not populating
      ... exec sp_defaultdb N'NT Authority\System', N'master' ... means either the network guys in my company who don't know SQL but are admins ... > needs this login to log into SQL Server and you can either add back this ... >> fetching U ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Indexing delay for one row.
      ... script and WAITFOR and varying the delay from 1 to 18 seconds. ... could you confirm your exact version of SQL Server that you are seeing ... It is possible that a change was made to the pooling frequence under SP3 to ... exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX' ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Changing DB Owner
      ... specified login must not already be a user in the database. ... USE MyDatabase ... EXEC sp_changedbowner 'SQLAdmin' ... SQL Server MVP ...
      (microsoft.public.sqlserver.server)
    • Re: Casting to VARCHAR(MAX)
      ... But when you do the EXEC, the statement the EXEC passes to SQL Server looks like ... So the string that gets put into #TEMP1 will be truncated to 8000 characters. ... that will force the whole expression concatenating the strings to return VARCHARand you would get the whole string inserted into the table. ...
      (microsoft.public.sqlserver.programming)
    • Re: storing and searching office docs in SQL
      ... You CAN both store and search the contents of the MS Word ... files stored in an SQL Table's FT-enable IMAGE column, ... FTS CONTAINS or FREETEXT to search the contents of that MS word document: ... exec sp_fulltext_database 'enable' -- only do this once! ...
      (microsoft.public.sqlserver.programming)