Automate New Logins Creation

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/automatenewloginscreation.asp

  • Tatsu

    SSCertifiable

    Points: 7824

    Very nice article.

    Another thing you can do if the password generator is a command line application is use xp_cmdshell to run the application and output the results to a table. I learned about this technique from an article I read here and use it to get a directory listing of backup files on the disk to be compared to the backup history tables in msdb.

    When I build a new application, I script out the logins from the development server and provide them to the DBA in a script. The DBA can change the passwords if necessary but the logins are assured of having the same SID regardless of which environment we're working in. This makes it really easy to restore a production copy of the database on the development server.

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Yelena Varshal

    SSC-Dedicated

    Points: 34270

    Good Article.

    I agree with Bryant Byrd who suggests running xp_cmdshell. I would run it as a last step to run a VB script to send SMTP email to a user to notify him/her of the new password. Remember the original requirement from the beginning of the article? For those choosen who have Excange on the network and can use SQL Mail they can use that. Or use SMTPDLL third party product mentioned here a couple of times.

    Yelena

    Regards,Yelena Varsha

  • Charley Jones

    SSC Enthusiast

    Points: 103

    Kudos on the use of openrowset to test the userid/password combination.  Always great to check your work, especially if you can automate that check!

    Charley Jones
    A+, ITIL, MCAD, MCDBA, MCITP: Sql Server 2005 Adminstration, MCT,
    MCTS: Sql Server 2005, MCP, MCSA, MCSE, MOUS, PMP
    President and Founder S3oLV.Com: Sql Server Society of Las Vegas
    President and Founder LVXUG.Com: Las Vegas XNA Users Group

  • Kenneth Lee

    Ten Centuries

    Points: 1273

    Note that there is a typo in step three.  Drop the last ' mark.

    A couple of things.  This isn't general purpose, the only reason it works is because there aren't any spaces in any of the tables or the DB name.  Second thing, I intentionally went into the wrong DB and ran the code.  Yep, got the wrong tables listed.  Thought I could just exec ('use '+@DB_nme) but the scope wasn't long enough.  This will generate the script for any DB.  I haven't fixed all possible errors for data entry on @DB_nme.

    declare @DB_nme sysname

    set @DB_nme = '  pubs  '

    set NOCOUNT ON

    declare @sql nvarchar(500)

    if (CHARINDEX('[',@DB_nme, 0) = 0) set @DB_nme = '[' + rtrim(ltrim(@DB_nme)) + ']'

    set @sql = 'use ' + @DB_nme + '

    print ''set NOCOUNT ON''

    select ''select count(*) as ['' + table_name + ''] from '+ @DB_nme

        + '.dbo.['' + table_name + '']'' from INFORMATION_SCHEMA.TABLES'

    exec sp_executesql @sql

  • Kenneth Lee

    Ten Centuries

    Points: 1273

    This is my third iteration on this idea.  Produces 1 result set, documents the DB and table so this could be used on several DBs to produce a joined list:

    declare @DB_nme sysname

    set @DB_nme = '  pubs  '

    set NOCOUNT ON

    declare @sql nvarchar(900)

    if (CHARINDEX('[',@DB_nme, 0) = 0) set @DB_nme = '[' + rtrim(ltrim(@DB_nme)) + ']'

    set @sql = 'use ' + @DB_nme + '

    set NOCOUNT ON

    declare @sql nvarchar(500)

    create table #TablCnt (DBName sysname, TableName sysname, NumRows int)

    declare @crs cursor

    set @crs = cursor FAST_FORWARD FOR

    select ''insert into #TablCnt (DBName, TableName, NumRows)

    select ''''' + @DB_nme + ''''',''''['' + table_name + '']'''',count(*) from '+ @DB_nme

        + '.dbo.['' + table_name + '']'' from INFORMATION_SCHEMA.TABLES

    open @crs

    FETCH NEXT FROM @crs

    INTO @sql

    WHILE @@FETCH_STATUS = 0

    BEGIN

     exec (@sql)

     FETCH NEXT FROM @crs

     INTO @sql

    END

    close @crs

    deallocate @crs

    select * from #TablCnt

    '

    exec sp_executesql @sql

  • Kenneth Lee

    Ten Centuries

    Points: 1273

    There's a lot of quotes in this script.  Using pubs as a DB, the 900 character @sql will look like:

    use [pubs]

    set NOCOUNT ON

    declare @sql nvarchar(500)

    create table #TablCnt (DBName sysname, TableName sysname, NumRows int)

    declare @crs cursor

    set @crs = cursor FAST_FORWARD FOR

    select 'insert into #TablCnt (DBName, TableName, NumRows)

    select ''[pubs]'',''[' + table_name + ']'',count(*) from [pubs].dbo.[' + table_name + ']' from INFORMATION_SCHEMA.TABLES

    open @crs

    FETCH NEXT FROM @crs

    INTO @sql

    WHILE @@FETCH_STATUS = 0

    BEGIN

     exec (@sql)

     FETCH NEXT FROM @crs

     INTO @sql

    END

    close @crs

    deallocate @crs

    select * from #TablCnt

    On the first fetch into the 500 character @sql variable, it would look like:

    insert into #TablCnt (DBName, TableName, NumRows)

    select '[pubs]','[authors]',count(*) from [pubs].dbo.[authors]

    Dumb me.  I don't have to have the "use [pubs]" code.  I just need to make "from INFORMATION_SCHEMA.TABLES" read like "from [pubs].INFORMATION_SCHEMA.TABLES".  I still think I need the two layers of code to get it to work and it's not worth the work to change it now.

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Thanks, it is a good idea to automate even more and add email option as additional step

     

    Leo Peysakhovich

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Thank you. It is always intresting to get new and additional ideas from the professionals

  • LP-181697

    SSC Eights!

    Points: 966

    Thanks for the thoughts. I am always trying to outline some ideas and general ways to look into a problem. It is possible to create a batch process or stored procedure to accomplish this task and place a database name as one of the parameters. But the whole idea of the article is to show the way for the automation. Anyway this is great idea and great comment. Thanks.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply