Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scripting Logins nightly Expand / Collapse
Author
Message
Posted Monday, June 1, 2009 2:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:34 AM
Points: 378, Visits: 1,036
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
Posted Monday, June 1, 2009 8:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
Posted Tuesday, June 2, 2009 8:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:34 AM
Points: 378, Visits: 1,036
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
Posted Tuesday, June 2, 2009 8:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 1,510, Visits: 8,470

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
Posted Tuesday, June 2, 2009 7:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
Posted Monday, June 8, 2009 9:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:34 AM
Points: 378, Visits: 1,036
I thought sp_help_revlogin was no longer available in SQL2k8.
Post #730764
Posted Tuesday, June 9, 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: Yesterday @ 12:09 PM
Points: 971, Visits: 979
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
Posted Tuesday, June 9, 2009 9:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:34 AM
Points: 378, Visits: 1,036
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
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