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