#0359 – SQL Server – Script to reset a SQL Login


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

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

Advertisements

2 thoughts on “#0359 – SQL Server – Script to reset a SQL Login

  1. Jeff Moden

    Thanks for sharing the script but I’m really hoping that you used a much more complex password and I’m really hoping that not all the servers where given the same SA password and I’m really hoping that the SA login is disabled on every server and you shouldn’t have need the SA password to begin with. πŸ˜‰

    Liked by 1 person

    Reply
    1. nakulvachhrajani Post author

      Hello! Glad to hear from you, Jeff! At least in our development environments, we use much stronger passwords (different for each instance) and on most servers the “sa” login is disabled. The only places where it is enabled are older SQL instances that run databases for the legacy (>15 yrs old!) on-premise applications that we support.

      As a general rule, I agree to all your points – complex passwords, disable “sa” login and different servers to have different passwords for the same login.

      Like

      Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s