SSIS package to Create backup of Logins with Password.
This package will works like backup of your login script with password.
The script also generates script for server level roles. For this you need to create two procedures in master database.
Step1 : Create supporting stored procedures in Master database.
Script for “[DBO].[SP_HEXADECIMAL]”
USE MASTER
GO
/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date: 27-03-2013 17:49:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DBO].[SP_HEXADECIMAL] @Binvalue VARBINARY(256),
@Hexvalue VARCHAR (514) OUTPUT
AS
DECLARE @Charvalue VARCHAR (514)
DECLARE @I INT
DECLARE @Length INT
DECLARE @Hexstring CHAR(16)
SELECT @Charvalue = '0x'
SELECT @I = 1
SELECT @Length = DATALENGTH (@Binvalue)
SELECT @Hexstring = '0123456789ABCDEF'
WHILE ( @I <= @Length )
BEGIN
DECLARE @Tempint INT
DECLARE @Firstint INT
DECLARE @Secondint INT
SELECT @Tempint = CONVERT(INT, SUBSTRING(@Binvalue, @I, 1))
SELECT @Firstint = FLOOR(@Tempint / 16)
SELECT @Secondint = @Tempint - ( @Firstint * 16 )
SELECT @Charvalue = @Charvalue
+ SUBSTRING(@Hexstring, @Firstint+1, 1)
+ SUBSTRING(@Hexstring, @Secondint+1, 1)
SELECT @I = @I + 1
END
SELECT @Hexvalue = @Charvalue
Script for “[DBO].[SP_HELP_REVLOGIN]”
USE [MASTER]
GO
/****** Object: StoredProcedure [dbo].[sp_help_revlogin] Script Date: 27-03-2013 17:48:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DBO].[SP_HELP_REVLOGIN] @Login_name SYSNAME = NULL
AS
SET NOCOUNT ON;
DECLARE @Name SYSNAME
DECLARE @Type VARCHAR (1)
DECLARE @Hasaccess INT
DECLARE @Denylogin INT
DECLARE @Is_disabled INT
DECLARE @Pwd_varbinary VARBINARY (256)
DECLARE @Pwd_string VARCHAR (514)
DECLARE @Sid_varbinary VARBINARY (85)
DECLARE @Sid_string VARCHAR (514)
DECLARE @Tmpstr VARCHAR (1024)
DECLARE @Is_policy_checked VARCHAR (3)
DECLARE @Is_expiration_checked VARCHAR (3)
DECLARE @Defaultdb SYSNAME
DECLARE @Createlogin TABLE
(
Logins VARCHAR(1024)
)
IF ( @Login_name IS NULL )
DECLARE LOGIN_CURS CURSOR FOR
SELECT P.Sid,
P.Name,
P.Type,
P.Is_disabled,
P.Default_database_name,
L.Hasaccess,
L.Denylogin
FROM SYS.Server_principals P
LEFT JOIN SYS.Syslogins L
ON ( L.Name = P.Name )
WHERE P.Type IN ( 'S', 'G', 'U' )
AND P.Name <> 'sa'
ELSE
DECLARE LOGIN_CURS CURSOR FOR
SELECT P.Sid,
P.Name,
P.Type,
P.Is_disabled,
P.Default_database_name,
L.Hasaccess,
L.Denylogin
FROM SYS.Server_principals P
LEFT JOIN SYS.Syslogins L
ON ( L.Name = P.Name )
WHERE P.Type IN ( 'S', 'G', 'U' )
AND P.Name = @Login_name
OPEN LOGIN_CURS
FETCH NEXT FROM LOGIN_CURS INTO @Sid_varbinary, @Name, @Type, @Is_disabled,
@Defaultdb, @Hasaccess, @Denylogin
IF ( @@fetch_status = -1 )
BEGIN
PRINT 'No login(s) found.'
CLOSE LOGIN_CURS
DEALLOCATE LOGIN_CURS
RETURN -1
END
WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status <> -2 )
BEGIN
IF ( @Type IN ( 'G', 'U' ) )
BEGIN -- NT authenticated account/group
SET @Tmpstr = 'CREATE LOGIN ' + QUOTENAME( @Name )
+ ' FROM WINDOWS WITH DEFAULT_DATABASE = ['
+ @Defaultdb + ']'
END
ELSE
BEGIN -- SQL Server authentication
-- obtain password and sid
SET @Pwd_varbinary = CAST(
LOGINPROPERTY(@Name, 'PasswordHash')
AS
VARBINARY (256))
EXEC SP_HEXADECIMAL
@Pwd_varbinary,
@Pwd_string OUT
EXEC SP_HEXADECIMAL
@Sid_varbinary,
@Sid_string OUT
-- obtain password policy state
SELECT @Is_policy_checked = CASE Is_policy_checked
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
ELSE NULL
END
FROM SYS.Sql_logins
WHERE Name = @Name
SELECT @Is_expiration_checked = CASE Is_expiration_checked
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
ELSE NULL
END
FROM SYS.Sql_logins
WHERE Name = @Name
SET @Tmpstr = 'CREATE LOGIN ' + QUOTENAME( @Name )
+ ' WITH PASSWORD = ' + @Pwd_string
+ ' HASHED, SID = ' + @Sid_string
+ ', DEFAULT_DATABASE = [' + @Defaultdb +
']'
IF ( @Is_policy_checked IS NOT NULL )
BEGIN
SET @Tmpstr = @Tmpstr + ', CHECK_POLICY = '
+ @Is_policy_checked
END
IF ( @Is_expiration_checked IS NOT NULL )
BEGIN
SET @Tmpstr = @Tmpstr + ', CHECK_EXPIRATION = '
+ @Is_expiration_checked
END
END
IF ( @Denylogin = 1 )
BEGIN -- login is denied access
SET @Tmpstr = @Tmpstr + '; DENY CONNECT SQL TO '
+ QUOTENAME( @Name )
END
ELSE IF ( @Hasaccess = 0 )
BEGIN -- login exists but does not have access
SET @Tmpstr = @Tmpstr + '; REVOKE CONNECT SQL TO '
+ QUOTENAME( @Name )
END
IF ( @Is_disabled = 1 )
BEGIN -- login is disabled
SET @Tmpstr = @Tmpstr + '; ALTER LOGIN ' + QUOTENAME(
@Name )
+ ' DISABLE'
END
INSERT INTO @Createlogin
SELECT @Tmpstr
END
FETCH NEXT FROM LOGIN_CURS INTO @Sid_varbinary, @Name, @Type,
@Is_disabled
,
@Defaultdb, @Hasaccess, @Denylogin
END
CLOSE LOGIN_CURS
DEALLOCATE LOGIN_CURS
INSERT INTO @Createlogin
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = N'''
+ P.Name + ''', @rolename = N''' + Pp.Name + ''''
FROM SYS.Syslogins Sl
LEFT JOIN SYS.Server_principals P
ON Sl.Name = P.Name
LEFT JOIN SYS.Server_role_members Roles
ON Roles.Member_principal_id = P.Principal_id
LEFT JOIN SYS.Server_principals Pp
ON Roles.Role_principal_id = Pp.Principal_id
LEFT JOIN SYS.Database_principals Dp
ON Sl.Name = Dp.Name
LEFT JOIN SYS.Database_role_members Droles
ON Droles.Member_principal_id = Dp.Principal_id
WHERE Pp.Name IS NOT NULL
SELECT *
FROM @Createlogin
RETURN 0
Step2 : All Other steps are same as you done in http://nirav.extreme-advice.com/2013/04/01/ssis-package-to-generate-mailprofile-script/
To create package you need one Data Flow Task, One Flat File Connection, One OLEDB Connection, and one variable
Please see in figure.

Step3 : Select Primary_Location under Connection Manger pane and hit F4.
Select Expression Property –> Click on expression button.
That will opens Property Expressions Editoer –> Select ConnectionString under property and paste below code for that expression.
@[User::FolderName] + "<a href="file://mailprofile/">\\MailProfile</a>_" + (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".sql"

Step4 : Drag one OLE DB Source and one Flat File Destination.
Right click on Data Flow Task –> Edit.

Step5 : Right click on OLEDB Source Editor — > Edit
Select your local machine connection.
Select SQL Command in Data Access mode.

Click on Columns and configure column.

Step6 : Right Click on Flat File Destination and select “Primary_Location” to flat file connection manager.

Click on Mappings to map column.

You can schedule this package in job to run every day or every week as you needed.
This script is very useful in disaster recovery to backup logins with password and initial server role.
Regards,
Nirav Gajjar