Automating the Transfer of Dynamics GP Databases – Part 3

In Parts 1 and 2 we created scripts that backup all of the Dynamics GP databases and then restore them to another SQL instance.  Once that’s done we’ll need refresh security.  To do that we need to transfer the SQL logins, except sa, used by Dynamics GP.

The following SQL script creates a login script that can be run against the second SQL to create the logins, including their original passwords and SIDS.

--  Ensure that when running this script using SQLCMD.EXE that you set the
--  database to master.
--
--  Setting NOCOUNT on prevents the row count message from appearing at the bottom.
--  Note that the SQL statements do not include column headers.  This makes
--  the entire output of the results window executable.

SET NOCOUNT ON

IF  EXISTS (
        SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[fn_Hexadecimal]')
                AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_Hexadecimal]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Limey
-- Create date: 7/16/2009
-- Description:   
--   This function converts a varbinary to a
--   hexadecimal string value.
--   It is used to convert a password hash and
--   login SID to a string representation.
-- =============================================
CREATE FUNCTION dbo.fn_Hexadecimal
(
    -- Add the parameters for the function here
    @binvalue varbinary(256)
)
RETURNS varchar (514)
AS
BEGIN
    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

    -- Return the result of the function
    RETURN @charvalue
END
GO

--  This common table expression returns all enabled Dynamics GP
--  logins and their properties.
WITH DynamicsGPLogins
AS (
    SELECT
            SID = dbo.fn_Hexadecimal(ServerPrincipals.sid)
            ,UserName = ServerPrincipals.name
            ,DefaultDatabaseName = ServerPrincipals.default_database_name
            ,IsPolicyChecked =
                CASE
                    WHEN SQLLogins.is_policy_checked  = 1 THEN 'ON'
                    ELSE 'OFF'
                END
            ,IsExpirationChecked =
                CASE
                    WHEN SQLLogins.is_expiration_checked  = 1 THEN 'ON'
                    ELSE 'OFF'
                END
            ,PasswordHash = dbo.fn_Hexadecimal(CAST(LOGINPROPERTY(ServerPrincipals.name, 'PasswordHash') AS varbinary (256)))
        FROM sys.server_principals AS ServerPrincipals
            LEFT JOIN sys.syslogins AS Logins
                ON Logins.name = ServerPrincipals.name
            LEFT JOIN sys.sql_logins AS SQLLogins
                ON ServerPrincipals.name = SQLLogins.name
        WHERE ServerPrincipals.type IN ( 'S', 'G', 'U' )
            AND ISNULL(Logins.hasaccess, 0) = 1
            AND ISNULL(Logins.denylogin, 1) = 0
            AND ISNULL(ServerPrincipals.is_disabled, 1) = 0
            AND ServerPrincipals.name <> 'sa'
            AND ServerPrincipals.name IN (
                    SELECT
                            USERID
                        FROM DYNAMICS.dbo.SY01400)
)

--  This select statement uses the list of Dynamics GP
--  logins to create a create login script.
SELECT
'
IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N''' + UserName + ''')
    DROP LOGIN ' + QUOTENAME(UserName) + '
GO

CREATE LOGIN ' + QUOTENAME(UserName) + '
    WITH PASSWORD = ' + PasswordHash + ' HASHED,
    SID = ' + SID + ',
    DEFAULT_DATABASE = ' + DefaultDatabaseName + ',
    CHECK_POLICY = ' + IsPolicyChecked + ',
    CHECK_EXPIRATION = ' + IsExpirationChecked + '
GO
'
    FROM DynamicsGPLogins

There are two basic sections to this query.  The first creates a scalar function in the master database that converts a varbinary to a varchar.  This is used to convert the SID and password hash to a text representation that can be put in the CREATE LOGIN statement.

The second section of the script actually creates the login script.  There are two parts to this are:

• The common table expression that returns SQL logins associated with Dynamics GP (from the DYNAMICS SY01400 table) and the associated properties.  It filters out any logins that are disabled or have been denied access.
• The final part is a select that uses this common table expression to actually create the CREATE LOGIN statements.

This is a simplified version of the SQL login transfer script found in Microsoft Knowledgebase Article 918992 and will produce the following SQL script:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N'DYNSA')
    DROP LOGIN [DYNSA]
GO

CREATE LOGIN [DYNSA]
    WITH PASSWORD = 0x01008E7655067D77DE8FCC167C184C22B8B47BC3CA3CA2FFA86E HASHED,
    SID = 0x42F411B53282584D96B94C0B09491091,
    DEFAULT_DATABASE = master,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO

IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N'LESSONUSER1')
    DROP LOGIN [LESSONUSER1]
GO

CREATE LOGIN [LESSONUSER1]
    WITH PASSWORD = 0x0100F81A338610819AD7298F890F6DF93302BC329D14B0F23D9D HASHED,
    SID = 0xA33F190403906349BA4074FC68B7F883,
    DEFAULT_DATABASE = master,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO

IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N'LESSONUSER2')
    DROP LOGIN [LESSONUSER2]
GO

CREATE LOGIN [LESSONUSER2]
    WITH PASSWORD = 0x0100C093A6586BBC58FE0CB74886EC4F0AB442FC60766EF9805E HASHED,
    SID = 0xBF47CE1CB51C1A4CABC2A75980AAF78D,
    DEFAULT_DATABASE = master,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO

Note that since this creates logins with the same SIDs, they’ll automatically be associated with database users.

In Part 4 we’ll look at putting this all together.