Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Scripting Logins nightly
Add to briefcase

Scripting Logins nightly Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ggoble
Posted Monday, June 01, 2009 2:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:45 PM
Points: 366, Visits: 840
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
Post #726974
WayneS
Posted Monday, June 01, 2009 8:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #727097
ggoble
Posted Tuesday, June 02, 2009 8:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:45 PM
Points: 366, Visits: 840
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.
Post #727458
Alvin Ramard
Posted Tuesday, June 02, 2009 8:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031, Visits: 7,383

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

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.
Post #727467
WayneS
Posted Tuesday, June 02, 2009 7:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #727828
ggoble
Posted Monday, June 08, 2009 9:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:45 PM
Points: 366, Visits: 840
I thought sp_help_revlogin was no longer available in SQL2k8.
Post #730764
S. Kusen
Posted Tuesday, June 09, 2009 9:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873
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
Post #731529
ggoble
Posted Tuesday, June 09, 2009 9:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:45 PM
Points: 366, Visits: 840
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.
Post #731553
steve block
Posted Monday, June 15, 2009 8:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 21, 2012 3:13 PM
Points: 516, Visits: 1,563
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


Post #735073
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.