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:
Ensures that the SQL Server instances uses Mixed mode authentication (i.e. both Windows and SQL Logins are allowed)
Ensures that the login referenced by the user is a SQL Login
Ensures that the login is enabled or it is disabled and the user has chosen to activate users
A valid SQL login exists with the name provided in the script
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
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.
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. 😉
LikeLiked by 1 person
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.