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