Scripting Logins nightly

  • Does anyone know of a way that I can script out all of my logins on a nightly basis? Previously I used sp_help_revlogin via CMDExec

  • Do it in a job.

    I think that you can also make an SSIS pkg to transfer logins, which would then be run, you guessed it, as a job.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I don't want to transfer them. I want to script them out to a .sql file so that I could restore them if the server catches fire. The transfer login task only lets you select a destination sql server.

  • Aren't the logins stored in the Master database? Wouldn't backing up the Master database give you what you need?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • greggoble2 (6/2/2009)


    I don't want to transfer them. I want to script them out to a .sql file so that I could restore them if the server catches fire. The transfer login task only lets you select a destination sql server.

    Okay. You can still set up a job to run sp_help_revlogin

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I thought sp_help_revlogin was no longer available in SQL2k8.

  • There is a 2005 version which should work the same in 2008.

    This URL takes you to that article:

    http://support.microsoft.com/kb/918992

    I haven't tested this out on 2008 yet, but the syntax and objects I believe are the same.

    Regards,

    Steve

  • SK (6/9/2009)


    There is a 2005 version which should work the same in 2008.

    This URL takes you to that article:

    http://support.microsoft.com/kb/918992

    I haven't tested this out on 2008 yet, but the syntax and objects I believe are the same.

    Regards,

    Steve

    Thanks SK! I didn't know I had to run the script mentioned in the KB article.

  • This it what I use nightly from a job in SS 2000 to create a script file that will rebuild the logins.

    osql -E -Sservername -Q"sp_help_revlogin" -o\\Sharename\DirName\rebuild_logins.sql -w512

    Like a previous poster mentioned, there is a version of sp_help_revlogin for 2005 and osql (from what I've heard) is no longer used in 2008 either.

    Oh well, if you haven't figured it out yet, this might point you in a direction.

    Steve

Viewing 9 posts - 1 through 8 (of 8 total)

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