A few days ago, we were changing the passwords of SQL Logins on our development SQL Server instances.

Because IT installed them and our Windows credentials were given sysadmin fixed server role access, we had hardly ever used the SQL logins. The result was that we had forgotten the original passwords for many of these instances. We therefore wrote the following script to reset the logins and I thought it to be a script that many of you may find useful.

The script below does the following:

1. Ensures that the SQL Server instances uses Mixed mode authentication (i.e. both Windows and SQL Logins are allowed)
2. Ensures that the login referenced by the user is a SQL Login
3. Ensures that the login is enabled or it is disabled and the user has chosen to activate users
4. A valid SQL login exists with the name provided in the script
5. Maintains the properties: MUST_CHANGE, CHECK_POLICY, CHECK_EXPIRATION

Disclaimer: This script are provided “as-is” and without warranty. Please use this only on your development and QA environments after thorough testing.

DECLARE @loginToResetPassword NVARCHAR(255) = N'sa';
DECLARE @newPasswordForLogin NVARCHAR(255) = N'Pa$$w0rd';
DECLARE @enableLoginIfDisabled BIT = 1;

/*********** NO USER CONFIGURABLE CODE BEYOND THIS POINT!! ****************/
SET NOCOUNT ON;
--Declarations of internal variables
BEGIN
    DECLARE @sqlStmnt NVARCHAR(MAX);
    DECLARE @loginIsMustChange INT;  --Per MSDN, this is an INT
    DECLARE @loginIsCheckPolicy BIT; --This will be computed based on the value of DaysUntilExpiration
    DECLARE @serverName NVARCHAR(255) = CAST(SERVERPROPERTY(N'ServerName') AS NVARCHAR(255));
END

--Validations
BEGIN
    --Check for mixed mode authentication
    IF (ISNULL(SERVERPROPERTY(N'IsIntegratedSecurityOnly'),1)=1)
    BEGIN
        PRINT N'<<<< INFO >>>> Environment uses Integrated Security (Windows Authentication) only.';
        PRINT SPACE(14) + N'Login passwords cannot be reset when Integrated Security is used.';
        GOTO SCRIPT_END;
    END

    IF NOT EXISTS (SELECT * 
                   FROM sys.server_principals AS sp 
                   WHERE sp.type = N'S'        --SQL Logins only
                     AND ((sp.is_disabled = 1 AND @enableLoginIfDisabled = 1) OR sp.is_disabled = 0)
                     AND sp.name = @loginToResetPassword
                  )
    BEGIN
        PRINT N'<<<< INFO >>> Login reset cannot be performed because of one of the following reasons:';
        PRINT SPACE(14) + N'1. Login is not a valid SQL Login';
        PRINT SPACE(14) + N'2. The login is disabled and user has chosen not to enable it';
        PRINT SPACE(14) + N'3. The login does not exist';
        GOTO SCRIPT_END;
    END
END

--If we got here, it means that the login is valid, and we can reset it
BEGIN
    --We begin by collecting the login properties
    BEGIN
        --If DaysUntilExpiration = 0, login is either expired or it will expire the day we are running this query 
        --                       = -1, the local security policy in Windows never expires the password
        --                       = NULL the CHECK_POLICY or CHECK_EXPIRATION is OFF for the login 
        --                         or the OS does not support password policies
        SELECT @loginIsMustChange = CAST(LOGINPROPERTY(@loginToResetPassword,N'IsMustChange') AS INT),
               @loginIsCheckPolicy = CASE WHEN LOGINPROPERTY(@loginToResetPassword,N'DaysUntilExpiration') IS NULL THEN 0
                                          ELSE 1
                                          END;  
    END

    --Now we reset the password to the one chosen by the user
    SELECT @sqlStmnt = N'ALTER LOGIN ' + @loginToResetPassword + N' WITH PASSWORD = ''' + @newPasswordForLogin + '''';

    IF (@loginIsMustChange=1)
        SELECT @sqlStmnt += N' MUST_CHANGE';

    IF (@loginIsCheckPolicy=0)
        SELECT @sqlStmnt += N', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF';
    ELSE
        SELECT @sqlStmnt += N', CHECK_POLICY = ON, CHECK_EXPIRATION = ON';

    IF (@enableLoginIfDisabled = 1)
        SELECT @sqlStmnt += N'; ALTER LOGIN '+ @loginToResetPassword + N' ENABLE ;'

    ----Debug Point
    --SELECT @sqlStmnt;

    --Perform the update
    BEGIN TRY
        EXEC master.sys.sp_executesql @sqlStmnt = @sqlStmnt;

        PRINT N'<<<< INFO >>>> Password for login: ' + @loginToResetPassword + N' has been reset on SQL Server instance: ' + @serverName;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber,
               ERROR_SEVERITY() AS ErrorSeverity,
               ERROR_STATE() AS ErrorState,
               ERROR_MESSAGE() AS ErrorMessage; 
        PRINT N'<<<< ALERT!! >>>> The process to reset a SQL login''s password failed.';
    END CATCH
END

SCRIPT_END:
PRINT N'<<<< INFO >>>> Login reset script completed. Please review statements prior to this for any messages.';
GO

References

• ALTER LOGIN statement: [Books On Line reference]
• LOGINPROPERTY() function: [Books On Line reference]
• SERVERPROPERTY() function: [Books On Line reference]

Until we meet next time,
Be courteous. Drive responsibly.