Tag Archives: HowTo

All topics “HowTo” in Microsoft SQL Server.

#0374 – SQL Server – Removing string terminators (“\0”) using REPLACE during string concatenation


Recently, I was called upon to troubleshoot a strange behaviour demonstrated by a data conditioning script that involved string concatenations. The script ran fine without any errors, but the script did not appear to be concatenating string.

The entire script for this post is shared towards the end of the post. Because the script involves creating specific data which would give away the root cause, I will not be presenting the snippets beforehand.

Assume that we have a simple table with two columns, “FirstName” and “LastName”:

Simple table with test data for string concatenation demo

Simple table with test data for string concatenation demo

The data conditioning script involved populating the “FullName” column in the table with a simple combination of the First and the Last names.

Output of String Concatenation Script demonstrating the problem

Output of String Concatenation Script demonstrating the problem

The Problem: If we look at the output carefully, there is a problem with the FullNames for rows # 2 and 4. Although the LastName is present, only the FirstName is seen in the concatenation result.

The Theory:

After about an hour of troubleshooting, we decided to check out the length of the strings in the table, and that’s when we hit gold. Although we could “see” only a couple of characters, the length was turning out to be a higher than what we expected.

As can be seen from the screenshot below, although the First Name “John” has a length of 4, we get 5 in the length. Similarly, although the FullName shows up as “John”, we get a length of 9.

Screenshot showing the length of the strings in the table

Screenshot showing the length of the strings in the table

This is the moment when the light bulb went off and we realized what was going on. The strings were inserted by a legacy application based on C/C++ code. In such legacy applications, we need to explicitly handle termination of strings by adding the string termination character. That would very well account for the presence of an additional character in the FirstName.

In order to explain the mystery behind the length of the FullName, let’s walk through the concatenation of a record.

  • FirstName = John, 4 characters
  • LastName = Doe, 3 characters
  • Expected length of FullName = First Name + a space + Last Name = 4 + 1 + 3 = 8 characters
  • Actual length of FullName = 9 characters

The difference can be accurately explained when we believe that the concatenation did actually happen – only thing is that we are unable to see the LastName part of the string because the system encounters the string termination character, causing it to stop displaying more characters from the string.

In order to confirm our theory, we replaced the string termination character with a hyphen (-), using the REPLACE function.

UPDATE pt
SET pt.FullName = REPLACE(pt.FullName COLLATE Latin1_General_BIN, CHAR(0) COLLATE Latin1_General_BIN, '-')
FROM @personTable AS pt;

In order for this to work in all environments, we changed the collation to binary when performing the replace (because ultimately, string terminators are just a set of bits when performing binary manipulation).

Finding and Replacing the String Terminator in a string.

Finding and Replacing the String Terminator in a string.

As can be seen from the screenshot above, the REPLACE was successful, and we were able to see the entire string.

In Conclusion

  • When working with data created by legacy code, it is useful to understand how the code works. In this case, we realized that the string terminator was causing a problem and were able to overcome it – but it could have led to hours of troubleshooting (an option of re-creating data manually was also put on the table)
  • SQL Server, and T-SQL can be trusted when it comes to data manipulation. Almost always it’s the system or the human element that is missing something critical

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

Script for this post:

USE tempdb;
GO
--Creating the sample table
DECLARE @personTable TABLE (FirstName VARCHAR(50),
                            LastName  VARCHAR(50),
                            FullName  VARCHAR(100)
                           );

--Insert some test data
INSERT INTO @personTable (FirstName, LastName)
VALUES ('Nakul','Vachhrajani'),
       ('John' + CHAR(0),'Doe'),
       ('Jack','Smith'),
       ('FirstName' + CHAR(0),'LastName');

--Check out the data
SELECT pt.FirstName,
       pt.LastName
FROM @personTable AS pt;

--Perform the string concatenation
UPDATE pt
SET pt.FullName = pt.FirstName + ' ' + pt.LastName
FROM @personTable AS pt;


--Check out the data
SELECT pt.FirstName,
       pt.LastName,
       pt.FullName
FROM @personTable AS pt;

--Checking the length of the data
SELECT pt.FirstName,
       LEN(pt.FirstName) AS FirstNameLength,
       pt.LastName,
       LEN(pt.LastName) AS LastNameLength,
       pt.FullName,
       LEN(pt.FullName) AS FullNameLength
FROM @personTable AS pt;

--Confirming presence of string termination characters
SELECT pt.FirstName,
       LEN(pt.FirstName) AS FirstNameLength,
       CHARINDEX(CHAR(0),pt.FirstName,1) AS LocationOfStringTerminator,
       pt.LastName,
       LEN(pt.LastName) AS LastNameLength,
       pt.FullName,
       LEN(pt.FullName) AS FullNameLength,
       CHARINDEX(CHAR(0),pt.FullName,1) AS LocationOfStringTerminatorInFullName
FROM @personTable AS pt;

--Replace the string termination character with a hyphen
UPDATE pt
SET pt.FullName = REPLACE(pt.FullName COLLATE Latin1_General_BIN, CHAR(0) COLLATE Latin1_General_BIN, '-')
FROM @personTable AS pt;

--Confirming that string termination characters are no longer present
SELECT pt.FirstName,
       LEN(pt.FirstName) AS FirstNameLength,
       CHARINDEX(CHAR(0),pt.FirstName,1) AS LocationOfStringTerminator,
       pt.LastName,
       LEN(pt.LastName) AS LastNameLength,
       pt.FullName,
       LEN(pt.FullName) AS FullNameLength,
       CHARINDEX(CHAR(0),pt.FullName,1) AS LocationOfStringTerminatorInFullName
FROM @personTable AS pt;
GO
Advertisement

#0367 – SQL Server – Fetching connection session options using SESSIONPROPERTY


A couple of years ago, I wrote an article which demonstrated that the SET options defined for a connection influence query execution and query results. One of the questions that came up in the research was how to determine which SET options are being used for a given session. It was then when I learnt about the system function – SESSIONPROPERTY().

The SESSIONPROPERTY() function can be used to return the current session value of the following SET options:

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • NUMERIC_ROUNDABORT
  • QUOTED_IDENTIFIER

Here’s an example:

SELECT SESSIONPROPERTY('ANSI_NULLS') AS IsAnsiNullsOn,
       SESSIONPROPERTY('ANSI_PADDING') AS IsAnsiPaddingOn,
       SESSIONPROPERTY('ANSI_WARNINGS') AS IsAnsiWarningsOn,
       SESSIONPROPERTY('ARITHABORT') AS IsArithAbortOn,
       SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS IsConcatNullYieldsNull,
       SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS IsNumericRoundabortOn,
       SESSIONPROPERTY('QUOTED_IDENTIFIER') AS IsQuotedIdentifierOn;
GO
Output demonstrating the usage of SESSIONPROPERTY() system function

Output demonstrating the usage of SESSIONPROPERTY() system function

Please do keep in mind that the SET options take effect based on a combination of server-level, database-level, and user-specified options. Hence, manipulating these options needs to be done with care.

Further Reading

  • SQL Server Stored Procedures and SET options [Link]
  • SESSIONPROPERTY [MSDN Link]

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

#0365 – SQL Server – Auditing – Fetching properties of the current connection using CONNECTIONPROPERTY


Most enterprise applications implement auditing in areas of application that have business importance, data cleanup and data quality improvement. It therefore becomes important to track the following:

  1. From where a particular data manipulation request is coming? (Client Name, IP address, etc)
  2. What is the connection mechanism used?
  3. Which port is being used by the connection?
  4. What is the payload type (TSQL/SOAP or other)

While Microsoft SQL Server already provides us a way to access connection properties using the DMV – sys.dm_exec_connections, the challenge is in finding the required information for the current connection only, and not for all connections to the server.

The solution is therefore to use the system function – CONNECTIONPROPERTY().

CONNECTIONPROPERTY returns the connection properties for the connection on which the request came in. Hence, unless the auditing runs on a separate connection, we would always get information about the connection that is actually performing the data manipulation. This makes the function an ideal method to implement auditing within triggers.

Allow me to demonstrate it with 2 examples – one where the connection was done using Shared Memory (my SSMS client and the database engine are on the same machine) and using TCP/IP.

SELECT CONNECTIONPROPERTY('net_transport') AS TransportProtocol,
       CONNECTIONPROPERTY('protocol_type') AS PayloadType, 
       CONNECTIONPROPERTY('auth_scheme') AS AuthenticationUsed,
       CONNECTIONPROPERTY('local_net_address') AS TargetServerIPAddressIfTCPUsed,
       CONNECTIONPROPERTY('local_tcp_port') AS TargetServerTCPPortIfTCPUsed,
       CONNECTIONPROPERTY('client_net_address') AS ClientAddress,
       CONNECTIONPROPERTY('physical_net_transport') AS PhysicalTransportProtocol;
SQLTwins, Post #0365 - Output of CONNECTIONPROPERTY() when using a Shared Memory connection

Output of CONNECTIONPROPERTY() when using a shared memory connection

SQLTwins, Post #0365 - Output of CONNECTIONPROPERTY() when using a TCP/IP connection

Output of CONNECTIONPROPERTY() when using a TCP/IP connection

As demonstrated, the CONNECTIONPROPERTY() is the easiest way (in my opinion) to get the properties of the current connection for auditing purposes.

Further Reading

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

#0360 – SQL Server – sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified)


This post has been a long time coming. I am an advocate of using newer T-SQL and administrative features if situation and compatibility related business requirements allow. In implementation of this ideology the question always comes up from most of my team members as to why a change is required when the old syntax/methods work just as effectively. I often demonstrate the example provided below to them.

In development environments, we generally move databases across various instances via a simple detach-append process. The traditional approach is to use the system stored procedure sp_attach_db. When CREATE DATABASE was enhanced to include support of attaching a database, one of the obvious differences that came up was the number of database files that it supported for the ATTACH operation.

The sp_attach_db system stored procedure can only attach a maximum of 16 database files.

In this scenario, the newer option of CREATE DATABASE is a blessing for very large databases where we have multiple files spread across multiple file-groups and multiple drives.

Allow me to demonstrate this with an example.

USE master ;
GO
--Safety Check
IF DB_ID('AttachDBTest') IS NOT NULL 
    DROP DATABASE AttachDBTest ;
GO

--Create the test database that has more than 16 files
CREATE DATABASE AttachDBTest ON PRIMARY  
    (NAME = Attach1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach2,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach3,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach4,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach5,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach6,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach7,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach8,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach9,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach10,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    (NAME = Attach11,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat11.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach12,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach13,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach14,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach15,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach16,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach17,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach18,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach19,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach20,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) 
LOG ON 
   (NAME = Attachlog1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
--FOR ATTACH
;
GO

--Create Test Table
USE AttachDBTest;
GO
IF OBJECT_ID('dbo.TestTable','U') IS NULL
BEGIN
    CREATE TABLE dbo.TestTable (RecId INT NOT NULL IDENTITY(1,1),
                                RecValue NVARCHAR(20) DEFAULT 'AttachTest'
                               );

END
GO

--Insert Test Data
USE AttachDBTest;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
    EXEC sp_executesql N'INSERT INTO dbo.TestTable (RecValue) DEFAULT VALUES;';
END
GO 10

--Validate Test Data
USE AttachDBTest;
GO
SELECT * FROM dbo.TestTable;
GO

As seen from the screenshots below, the database creation was successful, and so was the insertion of test data.

image

image 

Next, let us detach the database using the system stored procedure sp_detach_db.

--Detach the database
USE master;
GO
EXEC sp_detach_db @dbname = 'AttachDBTest', @skipchecks = 'true' ;
GO

--Confirm that the database has been detached
USE master;
GO
SELECT DB_ID('AttachDBTest') AS AttachDBTestDBID;
GO

image

Now, let us attach the database again with the sp_attach_db system stored procedure.

--Attach the database
USE master;
GO
EXEC sp_attach_db 
    @dbname = 'AttachDBTest', 
    @filename1 = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    @filename2 = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    @filename3 = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    @filename4 = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    @filename5 = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    @filename6 = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    @filename7 = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    @filename8 = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    @filename9 = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    @filename10 = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    @filename11 = 'C:SQLDatabasesDBFileAttachTestAttachdat11.ndf',
    @filename12 = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    @filename13 = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    @filename14 = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    @filename15 = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    @filename16 = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    @filename17 = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    @filename18 = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    @filename19 = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    @filename20 = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    @filename21 = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf';
GO

Msg 8144, Level 16, State 2, Procedure sp_attach_db, Line 0
Procedure or function sp_attach_db has too many arguments specified.

Now, let us try to attach the database via the CREATE DATABASE statement with the FOR ATTACH option.

--Attach the database using the CREATE DATABASE statement with the FOR ATTACH clause
USE master;
GO
CREATE DATABASE AttachDBTest ON PRIMARY  
    (NAME = Attach1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach2,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach3,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach4,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach5,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach6,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach7,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach8,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach9,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach10,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    (NAME = Attach11,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat11.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach12,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach13,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach14,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach15,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach16,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach17,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach18,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach19,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach20,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) 
LOG ON 
   (NAME = Attachlog1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
FOR ATTACH
;
GO

--Confirm that the database has been detached
USE master;
GO
SELECT DB_ID('AttachDBTest') AS AttachDBTestDBID;
GO

--Validate Test Data
USE AttachDBTest;
GO
SELECT * FROM dbo.TestTable;
GO

image

This example just demonstrates that when Microsoft recommends a switch from one syntax to another, there has to be some reason behind it and that newer options should be evaluated for replacing older ones.

References

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

#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.