Scripting Logins nightly

Question

Post reply

Scripting Logins nightly

ggoble

Hall of Fame

Points: 3337
More actions
June 1, 2009 at 2:23 pm

#210319

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

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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

ggoble Hall of Fame Points: 3337 More actions · Answer 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.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

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]

WayneS SSC Guru Points: 95460 More actions · Answer 4

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

ggoble Hall of Fame Points: 3337 More actions · Answer 5

I thought sp_help_revlogin was no longer available in SQL2k8.

S. Kusen SSChampion Points: 10936 More actions · Answer 6

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

ggoble Hall of Fame Points: 3337 More actions · Answer 7

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.

steve block SSCertifiable Points: 7698 More actions · Answer 8

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