quote:

---

CREATE PROCEDURE spadmin_util_ScriptLogins
/********************************************************************************/
/* Stored Procedure: spadmin_util_ScriptLogins */
/* Creation Date: 01/22/2002 */
/* Written by: Jeff Banschbach */
/* Consultant, MCDBA */
/* Greenbrier & Russel */
/* */
/* Purpose: Generates a script that can be used to recreate all */
/* logins on a server. This script stores passwords in an */
/* encrypted format. */
/* */
/* Input Paramters: None */
/* */
/* Output Parameters: None */
/* */
/* Return Status: None */
/* */
/* Usage */
/* exec spadmin_util_ScriptLogins */
/* ex: exec spadmin_util_ScriptLogins */
/* */
/* Local Variables: */
/* @loginname - Cursor Variable for current loginname */
/* @binpass - Cursor Variable for current password in binary form */
/* @charpass - Encrypted Binary password in Char form */
/* @dbname - Cursor Variable for current users default database */
/* @language - Cursor Variable for current users default language */
/* @i - Loop Variable */
/* @length - Loop Variable */
/* @hexstring - Temp hex value */
/* @tempint - Placeholder */
/* @firstint - Placeholder */
/* @secondint - Placeholder */
/* */
/* Called By: */
/* SQL Server Job */
/* */
/* Calls: */
/* spadmin_util_hexadecimal */
/* */
/* Updates: */
/* Date Author Purpose */
/* */
/********************************************************************************/

AS

----Declare Local Variables
DECLARE @loginname sysname
DECLARE @binpass varbinary(255)
DECLARE @charpass varchar(255)
DECLARE @dbname sysname
DECLARE @language sysname

DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int

SET NOCOUNT ON

----Generate Script to Add NT Groups and NT Users then Set Their Default Database
SELECT 'exec master.dbo.sp_grantlogin "' + sl.loginname + '"' + char(13) +
'GO' + char(13) +
'exec master.dbo.sp_defaultdb "' + sl.loginname + '", "' + sl.dbname + '"' + char(13) + 'GO' + char(13)
FROM master.dbo.syslogins sl
WHERE isntuser = 1 or isntgroup = 1

----Declare Cursor to Store List of SQL Server Logins
DECLARE user_cursor CURSOR FOR
SELECT loginname, convert(varbinary(32), password), dbname, language
FROM master.dbo.syslogins
WHERE isntuser = 0 and isntgroup = 0
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @loginname, @binpass, @dbname, @language
WHILE @@fetch_status = 0
BEGIN
----Generate Script to Add SQL Server Logins with their Default Database and Language
-- EXEC admin.dbo.spadmin_util_hexadecimal @binpass, @charvalue = @charpass OUTPUT
SELECT @charpass = '0x'
SELECT @i = 1
SELECT @length = datalength(@binpass)
SELECT @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
SELECT @tempint = convert(int, substring(@binpass,@i,1))
SELECT @firstint = floor(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charpass = @charpass +
substring(@hexstring, @firstint+1, 1) +
substring(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
end
SELECT 'exec master.dbo.sp_addlogin "' + @loginname + '", ' + @charpass + ', "' + @dbname + '", "' + @language + '", @encryptopt = "skip_encryption"' + char(13) + 'GO' + char(13)
FETCH NEXT FROM user_cursor INTO @loginname, @binpass, @dbname, @language
END
----Clean Up Cursor Variable
CLOSE user_cursor
DEALLOCATE user_cursor

SET NOCOUNT OFF

---