SQL LOGIN PASSWORD MANAGER

Right before I went on vacation, in an audit, we discovered that occassioanlly there was a SQL Login that was slipping through the process and its password was not getting updated in a timely manner. It has also been a bear trying to remember where all the various service account logins are used and who the stakeholder is, responsible for updating them regularly.

This builds on the Password Solution from my Dec 2022 blog post or it may also be used as a stand alone application to manage SQL Login password changes.

https://lovethesql.com/update-change-password-via-powershell/

I am using SQL Server 2019 and Visual Studio 2019 to build this solution. I’ve used SSMS for my SQL code and VS for the SSIS package and SSRS report.

GOAL

To create a way to list and manage SQL Logins across multiple server instances within my organization, give users the ability to add themselves as a stakeholder, email users a reminder to change their password, and create a report with password age.

To do this, I will:

Create tables and stored procedures that will reside in my AUDIT database on each server instance. Set up a nightly job that will gather Sql instance information and update it
Create tables and stored procedures to allow stakeholder management.
Create an SSIS package to move the data to a single server and merge/update records.
Send out an Email reminders to stakeholder(s) before a passwrod expires.
Create a report that lists SQL logins, Stakeholders and Password age.

STEP 1: Set up AUDIT Database

Each of my servers has a database named [Audit]. This is where the LoginRecord table will be kept and updated. In this section (part one) I’ve made a couple notes in case, in a future scenario, I wish to use this solution on a single instance rather than multiple instances.

In this table I’ve included an EMAIL column that I will not use. I’ve put it here in case working with a SINGLE instance and want to generate password reset reminders from this table instead of using the EMAIL column in my AuditDW database. (You’ll see this later.)

TABLES

USE [Audit];

CREATE TABLE [dbo].[LoginRecord](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](250) NULL,
	[LoginName] [varchar](250) NULL,
	[Email] [varchar](500) NULL,
	[IsSysAdmin] [bit] NULL,
	[IsActive] [bit] NULL,
	[sid] [varbinary](85) NULL,
	[Created] [datetime] NULL,
	[LastCheck] [datetime] NULL,
	[PasswordLastSet] [datetime] NULL,
 CONSTRAINT [PK_LoginRecord] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

STORED PROCEDURES

This will MERGE current SQL Instance data into the LoginRecord table in the AUDIT database. I will set it up to run nightly via the SQL Agent.

I’ve called this sproc LoginRecordMerge. In my database [AuditDW] there will be a similar sproc named LoginRecordMergerSSIS that will be used to merge data from all locations. For now, I am still working on a single SQL instance in the AUDIT database.

USE [Audit] 
GO

/****** Object:  StoredProcedure [dbo].[LoginRecordMerge]    Script Date: 8/16/2023 11:18:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20230816
-- Description:	This will MERGE current SQL Instance data into the LoginRecord table in the AUDIT database.
-- Ideally it should be setup to run nightly via the SQL Agent.
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[LoginRecordMerge] 
AS
BEGIN
	SET NOCOUNT ON;

	MERGE INTO dbo.LoginRecord AS trgt
	USING	( SELECT @@SERVERNAME AS ServerName, 
			L.Name AS LoginName, 
			L.sysadmin AS IsSysAdmin, 
			L.sid, 
			CONVERT(Datetime,LOGINPROPERTY(name, 'PasswordLastSetTime')) AS PasswordLastSet 
	FROM	master.dbo.syslogins as L
	WHERE L.Status = 9 and L.name not like 'NT %' and L.name not like '##%' and L.isntname = 0
	) AS src
	ON (trgt.ServerName = src.ServerName 
		AND trgt.Loginname = src.LoginName
		AND trgt.sid = src.sid
	)
	WHEN MATCHED THEN
		UPDATE SET IsSysAdmin = src.IsSysAdmin,
			PasswordLastSet = src.PasswordLastSet,
			LastCheck = getutcdate()
	WHEN NOT MATCHED THEN
		INSERT (ServerName, LoginName, IsSysAdmin, IsActive,sid, Created, LastCheck, PasswordLastSet)
		VALUES (src.ServerName, src.LoginName, src.IsSysAdmin, 1, src.sid, getutcdate(), getutcdate(), src.PasswordLastSet)
	WHEN NOT MATCHED BY SOURCE THEN
		UPDATE SET IsActive = 0, IsSysAdmin = 0, LastCheck = getutcdate();

	SELECT TOP(1000) * FROM dbo.LoginRecord;

END

In the case of a single instance scenario, I am creating an optional sproc users could use to add an email address.

USE [Audit] 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20230816
-- Description:	For SINGLESERVER SOLUTION USERS: This will update/add an email address for a LoginName
-- =============================================
CREATE PROCEDURE [dbo].[LoginRecordEmailUpsert] 
@ServerName varchar(250),
@LoginName varchar(250),
@Email varchar(250)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE	dbo.LoginRecord 
	SET		Email = @Email
	WHERE	ServerName = @ServerName
			AND LoginName = @LoginName;

	If @@ROWCOUNT > 0
	BEGIN
		SELECT TOP(1000) * FROM dbo.LoginRecord WHERE LoginName = @LoginName;
	END
	ELSE
	BEGIN
		SELECT 'LOGIN RECORD NOT FOUND.' AS Result;
	END

END
GO

SQL AGENT JOB

I will create an Agent job to update the password information in this table each night. This will be duplicated on each Sql Server instance I am auditing.

STEP TWO: AuditDW Tables and Procedures

In the next step I will create an SSIS package to move the data from multiple instances to a single DW (data warehouse) instance that can manage reports and send out email alerts. This is my plan for the structure.

I have duplicated the same LoginRecord table I used before (1), added a LoginRecordUse (2) table to store Stakeholder names and emails, and an ApplicationType (3) table. I will also add a LoginRecordStaging table for SSIS to stage data.

USE [AuditDW]
GO

CREATE TABLE [dbo].[LoginRecord](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](250) NULL,
	[LoginName] [varchar](250) NULL,
	[Email] [varchar](500) NULL,
	[IsSysAdmin] [bit] NULL,
	[IsActive] [bit] NULL,
	[sid] [varbinary](85) NULL,
	[Created] [datetime] NULL,
	[LastCheck] [datetime] NULL,
	[PasswordLastSet] [datetime] NULL,
 CONSTRAINT [PK_LoginRecord] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoginRecordStage](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](250) NULL,
	[LoginName] [varchar](250) NULL,
	[Email] [varchar](500) NULL,
	[IsSysAdmin] [bit] NULL,
	[IsActive] [bit] NULL,
	[sid] [varbinary](85) NULL,
	[Created] [datetime] NULL,
	[LastCheck] [datetime] NULL,
	[PasswordLastSet] [datetime] NULL,
 CONSTRAINT [PK_LoginRecordStage] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ApplicationType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ApplicationName] [varchar](250) NULL,
CONSTRAINT [PK_ApplicationType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoginRecordUse](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LoginRecordID] [int] NULL,
	[ApplicationTypeID] [int] NULL,
	[StakeholderName] [varchar](250) NULL,
	[Email] [varchar](500) NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_LoginRecordUse] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoginRecordUse]  WITH CHECK ADD  CONSTRAINT [FK_LoginRecordUse_ApplicationType] FOREIGN KEY([ApplicationTypeID])
REFERENCES [dbo].[ApplicationType] ([ID])
GO

ALTER TABLE [dbo].[LoginRecordUse] CHECK CONSTRAINT [FK_LoginRecordUse_ApplicationType]
GO

ALTER TABLE [dbo].[LoginRecordUse]  WITH CHECK ADD  CONSTRAINT [FK_LoginRecordUse_LoginRecord] FOREIGN KEY([LoginRecordID])
REFERENCES [dbo].[LoginRecord] ([ID])
GO

ALTER TABLE [dbo].[LoginRecordUse] CHECK CONSTRAINT [FK_LoginRecordUse_LoginRecord]
GO

In order to test my solution, I’m going to go ahead and add some data into the ApplicationType table.


-------------------
--- DATA:  Some sample data for the Application Types. You'll want to add other more specific applications, web services, api, etc to this list.
---------------------

USE [AuditDW]
GO
SET IDENTITY_INSERT [dbo].[ApplicationType] ON 
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (1, N'SQL Server')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (2, N'SQL Configuration Manager')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (3, N'SSRS Configuration Manager')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (4, N'PowerBi Configuration Manager')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (5, N'PowerShell')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (6, N'Windows Task Scheduler')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (7, N'SQL Login User')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (8, N'MS AD User')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (9, N'MS AD Service Account User')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (10, N'MS AD Group')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (11, N'Windows Service')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (12, N'OS')
GO
INSERT [dbo].[ApplicationType] ([ID], [ApplicationName]) VALUES (13, N'Application - General')
SET IDENTITY_INSERT [dbo].[ApplicationType] OFF
GO

I will create a VIEW for this data as well.

USE AuditDW
GO

CREATE VIEW [dbo].[ApplicationTypeGetList]
AS

	SELECT		TOP(1000) ApplicationName, ID AS ApplicationTypeID
	FROM		dbo.ApplicationType
	ORDER BY	ApplicationName;

GO

I have borrowed a FUNCTION that my stored procedures will use to validate email addresses from: https://www.mssqltips.com/sqlservertip/6519/valid-email-address-check-with-tsql/

USE [AuditDW]
GO
CREATE FUNCTION dbo.ChkValidEmail(@EMAIL varchar(100))RETURNS bit as
BEGIN     
  DECLARE @bitEmailVal as Bit
  DECLARE @EmailText varchar(100)

  SET @EmailText=ltrim(rtrim(isnull(@EMAIL,'')))

  SET @bitEmailVal = case when @EmailText = '' then 0
                          when @EmailText like '% %' then 0
                          when @EmailText like ('%["(),:;<>\]%') then 0
                          when substring(@EmailText,charindex('@',@EmailText),len(@EmailText)) like ('%[!#$%&*+/=?^`_{|]%') then 0
                          when (left(@EmailText,1) like ('[-_.+]') or right(@EmailText,1) like ('[-_.+]')) then 0                                                                                    
                          when (@EmailText like '%[%' or @EmailText like '%]%') then 0
                          when @EmailText LIKE '%@%@%' then 0
                          when @EmailText NOT LIKE '_%@_%._%' then 0
                          else 1 
                      end
  RETURN @bitEmailVal
END 
GO

STAKEHOLDERS can update their email or add a new record using this stored procedure (or by the SSRS report included later in this post).

USE [AuditDW]
GO
/****** Object:  StoredProcedure [dbo].[LoginRecordMerge]    Script Date: 8/17/2023 9:38:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David Speight
-- Create date: 20230818
-- Description:	This will update an Email address associated with a login.
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[LoginUseUpdate] 
@LoginName varchar(250),
@StakeholderName varchar(250),
@Email varchar(500), -- BLANK allowed, in case no alerts should be sent.
@ApplicationTypeID INT,
@ServerName varchar(250) = 'ALL',  -- Use ALL to apply to all servers
@IsActive bit = 1
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Rows INT;
	DECLARE @ErrorMsg VARCHAR(500);
	-- Get unique columns:
	DECLARE @tLogins AS TABLE (ID INT NOT NULL IDENTITY(1,1), 
				ServerName VARCHAR(250),
				LoginName VARCHAR(250),
				LoginRecordID INT,
				ApplicationTypeID INT,
				StakeholderName VARCHAR(250),
				Email VARCHAR(500),
				IsActive bit);

	-- Verify Data  (Stakeholder, Email, ApplicationTypeID)
	SELECT	@ErrorMsg = (CASE WHEN  COUNT(*) = 0 THEN ' Invalid application ID entered. ' ELSE @ErrorMsg END)
	FROM	dbo.ApplicationType A
	WHERE	A.ID = @ApplicationTypeID;

SELECT	@ErrorMsg = (CASE WHEN  COUNT(*) = 0 THEN ' Invalid or inactive Login Name. ' ELSE @ErrorMsg END)
	FROM	dbo.LoginRecord
	WHERE	LoginName = @LoginName
		AND (ServerName = @ServerName OR @ServerName = 'ALL')
		AND IsActive = 1;

	SELECT @ErrorMsg = (CASE WHEN LEN(@Email) > 1 AND dbo.ChkValidEmail(@Email) = 0 THEN CONCAT(@ErrorMsg,' Invalid email entered. ') ELSE @ErrorMsg END);
	SELECT @ErrorMsg = (CASE WHEN LEN(@StakeholderName) < 2 THEN  CONCAT(@ErrorMsg,' Stakeholder name required. ') ELSE @ErrorMsg END);
	SELECT @ErrorMsg = (CASE WHEN LEN(@ServerName) = 0 THEN CONCAT(@ErrorMsg,' Server name required. ') ELSE @ErrorMsg END);

	IF  @ErrorMsg IS NULL
	BEGIN
	-- Get data we may need to manipulate
		INSERT INTO @tLogins
				(ServerName, LoginName, LoginRecordID, ApplicationTypeID, StakeholderName, Email, IsActive)
		SELECT	R.ServerName, R.LoginName, R.ID, @ApplicationTypeID, @StakeholderName, @Email, @IsActive
		FROM	dbo.LoginRecord R
		WHERE	(R.ServerName = @ServerName OR @ServerName = 'ALL')
			and (R.LoginName = @LoginName)
			AND (R.IsActive = 1);

		-- MERGE DATA INTO dbo,LoginRecordUse
		MERGE INTO dbo.LoginRecordUse as trgt
		USING (	SELECT	ServerName,
				LoginName,
				LoginRecordID,
				ApplicationTypeID,
				StakeholderName,
				Email,
				IsActive
			FROM	@tLogins
			) AS src
		ON (	trgt.LoginRecordID = src.LoginRecordID 
			AND trgt.ApplicationTypeID = src.ApplicationTypeID
			AND trgt.StakeholderName = src.StakeholderName )
		WHEN MATCHED THEN
			UPDATE SET trgt.Email = src.Email, trgt.IsActive = src.IsActive
		WHEN NOT MATCHED BY TARGET THEN
			INSERT (LoginRecordID,ApplicationTypeID,StakeholderName,Email,IsActive)
			VALUES (src.LoginRecordID,src.ApplicationTypeID,src.StakeholderName,src.Email,src.IsActive
			);

		-- RETURN Affectred dataset
		SELECT	U.LoginRecordID,
			R.ServerName,
			R.LoginName,
			U.ApplicationTypeID,
			A.ApplicationName,
			U.StakeholderName,
			U.Email,
			U.IsActive
		FROM	dbo.LoginRecord R 
			INNER JOIN dbo.LoginRecordUse U ON R.ID = U.LoginRecordID
			INNER JOIN dbo.ApplicationType A ON U.ApplicationTypeID = A.ID
		WHERE	(U.StakeholderName = @StakeholderName)
			AND (U.ApplicationTypeID = @ApplicationTypeID)
			AND (R.LoginName = @LoginName)
			AND (R.ServerName = @ServerName OR @ServerName = 'ALL')
			AND R.IsActive = 1;
	END
	ELSE
	BEGIN
		SELECT @ErrorMsg
	END;
END

STEP THREE: SSIS Package

Without going into an hour of details on SSIS, here is a short overview of my package.

The package TRUNCATEs the LoginRecordUseStaging table. It gets a list of SQL Server connection strings and then loops through them to import the data into the staging table.

FOREACH SERVER LOOP

SET CONNECTION STRING

IMPORT LOGIN RECORDS: OLE DB Source

The second to last step in my SSIS package: MERGE DATA FROM STAGE will use a stored procedure, alternatively I could include the code directly the package. I’m using a procedure so I can easily make changes to it if I need, without having to debug and redeploy the package again.

USE [AuditDW]
GO
/****** Object:  StoredProcedure [dbo].[LoginRecordMergeFromStage]    Script Date: 8/17/2023 9:38:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Speight
-- Create date: 20230816
-- Description:	This will MERGE/MOVE  SQL Instance data FROM Staging INTO the LoginRecord table in the AUDITDW database.
-- This is used by an SSIS package.
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[LoginRecordMergerFromStage] 
AS
BEGIN
	SET NOCOUNT ON;

	MERGE INTO dbo.LoginRecord AS trgt
	USING	( SELECT	ServerName, 
				LoginName, 
				Email,
				IsSysAdmin, 
				IsActive,
				sid, 
				Created,
				LastCheck,
				PasswordLastSet 
		FROM	dbo.LoginRecordStage
			) AS src
	ON		(
			trgt.ServerName = src.ServerName 
			AND	trgt.Loginname = src.LoginName
			AND trgt.sid = src.sid
			)
	WHEN MATCHED THEN
		UPDATE SET	Email = src.Email,
				IsSysAdmin = src.IsSysAdmin,
				IsActive = src.IsActive,
				PasswordLastSet = src.PasswordLastSet,
				Created = src.Created,
				LastCheck= src.LastCheck
	WHEN NOT MATCHED THEN
		INSERT (ServerName,LoginName,Email,IsSysAdmin,IsActive,sid,Created,LastCheck,PasswordLastSet)
		VALUES (src.ServerName,src.LoginName,src.Email,src.IsSysAdmin,1,src.sid,src.Created,src.LastCheck,src.PasswordLastSet)
	WHEN NOT MATCHED BY SOURCE THEN
		UPDATE SET Email = null, IsActive = 0, IsSysAdmin=0, LastCheck = getutcdate();


END;
GO

Once I’ve tested and debugged my SSIS package, I will deploy it to the server and then set up a SQL Agent job to run it each night.

STEP FOUR: Emailer

I’ll create a SQL Agent job that will check and send out an email each night to stakeholders that have passwords that are due to expire.

USE AuditDW
GO


/****** Object:  StoredProcedure [dbo].[E_SqlLogin_ExpiringPassword]    Script Date: 8/21/2023 3:37:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20230822
-- Description:	Email to stakeholders for exiring/ed SQL Logins
-- Example use: EXEC  [dbo].[E_SqlLogin_ExpiringPassword] 60, 10, 'DEVOPS'
-- =============================================
CREATE OR ALTER   PROCEDURE [dbo].[E_SqlLogin_ExpiringPassword] 
@AgeLimit int = 60,
@ReminderDays int = 10,
@E_profile_name VARCHAR(50)
AS
BEGIN

	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	DECLARE @body1 VARCHAR(max);
	DECLARE @curEmail VARCHAR(500)
	DECLARE @curStakeholderName VARCHAR(250);
	DECLARE @bodyFIN VARCHAR(max);
	DECLARE @StyleSheet VARCHAR(1000);
	DECLARE @Header VARCHAR(max);
	DECLARE @GreenTitle VARCHAR(250);
	DECLARE @SubjectTxt   VARCHAR(50);	
	DECLARE @tData AS TABLE (	ID INT NOT NULL IDENTITY (1,1),
					GroupID INT,
					ServerName VARCHAR(250),
					LoginName VARCHAR(250),
					ApplicationName VARCHAR(250),
					StakeholderName VARCHAR(250),
					Email VARCHAR(500),
					PasswordLastSet DATETIME,
					Age INT)
	DECLARE @oLoop INT=0;
	DECLARE @EmailMin INT;
	SELECT @EmailMin = @AgeLimit - @ReminderDays;
	SELECT @StyleSheet = 
'<style><!--
@font-face
	{font-family:"Microsoft Sans Serif";
	panose-1:2 11 6 4 2 2 2 2 2 4;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}
--></style>',
	@Header = CONCAT('<p class="MsoNormal" style="background:white"><span style="font-size:16pt;color:#009A44;letter-spacing:-1.0pt">COVENT</span><span style="font-size:16pt;color:#0C2340">BRIDGE GROUP</span></p>
<BR /><span style="font-size:16pt;color:#0C2340"><p><span style="font-size:16pt;color:#0C2340">SQL SERVER LOGIN Password expiring 
</br>',CONVERT(varchar(12),getutcdate()),' </span></p>
<p> The PASSWORD for the SQL Server Login(s) that you use are (about to be) expired. 
Please log into each server instance in the list below and change your password.
Using SSMS, you can use this sample code to easily accomplish the task.</p>
<p>USE master; EXEC sp_password @old=''My Old Password'', @new=''My New Password'',@loginame=''My Login Name'';</p>
<p>Thank you!</p>			
	'),
			@GreenTitle = '<p class="MsoNormal" style="background:white"><span style="font-size:16.0pt;font-family:&amp;quot;color:#009A44;letter-spacing:-1.0pt">',
			@SubjectTxt   = 'Sql Login Expiring';

	INSERT INTO @tData
	SELECT	-- Use dense_rank to create groups of same email & Stakeholder name combo.
		dense_rank() over (partition by 1 order by U.Email, U.StakeholderName) As GroupID,
		R.ServerName,
		R.LoginName,
		A.ApplicationName,
		U.StakeholderName,
		U.Email,
		R.PasswordLastSet,
		DATEDIFF(day,R.PasswordLastSet,getutcdate()) AS Age
	FROM	dbo.LoginRecord R 
		INNER JOIN dbo.LoginRecordUse U ON R.ID = U.LoginRecordID
		INNER JOIN dbo.ApplicationType A ON U.ApplicationTypeID = A.ID
	WHERE	U.IsActive = 1
		AND LEN(U.Email) > 4
		AND DATEDIFF(day,R.PasswordLastSet,getutcdate()) > @EmailMin
	ORDER BY Email, StakeholderName, -- GROUPING
		ServerName, LoginName, ApplicationName;
	
	SELECT @oLoop = MAX(GroupID) FROM @tData;
	WHILE @oLoop > 0  -- loop
	BEGIN
		SELECT	@curEmail = Email,
				@curStakeholderName = StakeholderName
		FROM @tData 
		WHERE GroupID = @oLoop
		GROUP BY Email, StakeholderName;
			
		SELECT @body1 =	cast( (
		SELECT td =  CONCAT(CONVERT(varchar(50),d.ServerName), '</td><td>', d.LoginName, '</td><td>', d.ApplicationName,'</td><td>', d.PasswordLastSet,  '</td><td style="color:',AgeColor,'">', convert(varchar(50),d.Age) )
		FROM (
			SELECT 								 
				ServerName, 
				LoginName,
				ApplicationName,
			CONVERT(VARCHAR(10),CONVERT(Date,PasswordLastSet)) AS PasswordLastSet,
				Age,
				(CASE WHEN Age > @AgeLimit THEN 'red' ELSE 'gold' END) AS AgeColor
			FROM		@tData
			WHERE		GroupID = @oLoop AND Age > @EmailMin
			GROUP BY	ServerName, 
					LoginName,
					ApplicationName,
			CONVERT(VARCHAR(10),CONVERT(Date,PasswordLastSet)),
					Age				
					) AS d
			FOR XML PATH( 'tr' ), type ) AS VARCHAR(MAX) );

		SELECT @body1 = (CASE WHEN LEN(@body1) IS NULL THEN '' ELSE		
				(CONCAT('<table cellpadding="2" cellspacing="2" border="0"><tr><th style="background:#009A44">Server</th><th style="background:#009A44">Login</th><th style="background:#009A44">Application</th><th style="background:#009A44">Password Last Set</th><th style="background:#009A44">Age</th></tr>'
	, replace( replace( @body1, '&lt;', '<' ), '&gt;', '>' )
	, '</table>')) END);
				
		SELECT @BodyFin = CONCAT( @StyleSheet,@Header,'<BR />',@GreenTitle,'SQL LOGIN EXPIRING</span></p><BR />', @body1,'<BR /><BR />',@GreenTitle,'Report generated by AuditDW.dbo.E_SqlLogin_ExpiringPassword.</span></p><BR />');
			
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @E_profile_name,
			@recipients = @curEmail,
			@body = @BodyFin,
			@subject = @SubjectTxt,
					@body_format = 'HTML';

		SELECT @oLoop = @oLoop - 1, @Body1='', @BodyFin='';
	END; -- loop

END;
GO

Sample Email:

STEP FIVE: SSRS Reporting

I will be using Visual Studio 2019 to crate my SSRS Report. The report will allow users to search and add new stakeholders and emails.

To start the report my form will have a few dropdown lists that users may use as filters. Here are the queries for those.


-- ServerNameList
SELECT 'All' AS ServerName, 1 AS RowID
UNION 
SELECT  DISTINCT ServerName, 2 AS RowID
FROM dbo.LoginRecord
ORDER BY 2,1

--LoginNameList
SELECT 'All' AS LoginName, 1 AS RowID
UNION 
SELECT  DISTINCT LoginName, 2 AS RowID
FROM dbo.LoginRecord
WHERE @ServerName = 'All' OR ServerName = @ServerName
ORDER BY 2,1


--ApplicationTypeList
SELECT 'All' AS ApplicationName, 1 AS RowID
UNION 
SELECT  DISTINCT ApplicationName, 2 AS RowID
FROM dbo.ApplicationType
ORDER BY 2,1


--Action
SELECT 'Search' AS Action
UNION 
SELECT 'Add Record' AS Action

Next, I will create a stored procedure that will handle the SEARCH, UPDATES, and INSERTS of records.


USE [AuditDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David Speight
-- Create date: 20230818
-- Description:	This is used along with the SSRS Report
-- EXEC dbo.LoginUseSSRS 'add record','all','admin','SQL Server','David Speight','david@MyCompanyNAME.com'
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[LoginUseSSRS] 
@Action VARCHAR(12), -- 'List' 'Search' 'Add Record'
@ServerName varchar(250) = 'All',  -- Use ALL to apply to all servers
@LoginName varchar(250) = 'All',
@ApplicationName varchar(250) = 'All',
@StakeholderName varchar(250) = 'All',
@Email varchar(500) = NULL, -- BLANK allowed, in case no alerts should be sent.
@IsActive bit = NULL
AS
BEGIN
	SET NOCOUNT ON;

SELECT	@ServerName =	(CASE WHEN @ServerName IS NULL OR LEN(TRIM(@ServerName)) = 0 THEN 'All' ELSE TRIM(@ServerName) END),
	@LoginName =	(CASE WHEN @LoginName IS NULL OR LEN(TRIM(@LoginName)) = 0 THEN 'All' ELSE TRIM(@LoginName) END),
	@ApplicationName = (CASE WHEN @ApplicationName IS NULL OR LEN(TRIM(@ApplicationName)) = 0 THEN 'All' ELSE TRIM(@ApplicationName) END),
	@StakeholderName = (CASE WHEN @StakeholderName IS NULL OR LEN(TRIM(@StakeholderName)) = 0 THEN 'All' ELSE TRIM(@StakeholderName) END),
	@Email = (CASE WHEN @Email IS NULL OR LEN(TRIM(@Email)) = 0 THEN NULL ELSE TRIM(@Email) END);

	DECLARE @ErrorMsg VARCHAR(500);
	SELECT	@ErrorMsg = (CASE WHEN  COUNT(*) = 0 AND @Action = 'Add Record' THEN ' Invalid application name entered. ' ELSE @ErrorMsg END)
	FROM	dbo.ApplicationType A
	WHERE	@ApplicationName IS NOT NULL 
		AND A.ApplicationName = @ApplicationName;

	SELECT @ErrorMsg = (CASE WHEN @Action != 'list' AND LEN(@Email) > 1 AND dbo.ChkValidEmail(@Email) = 0 THEN CONCAT(@ErrorMsg,' Invalid email entered. ') ELSE @ErrorMsg END);
	SELECT @ErrorMsg = (CASE WHEN (@Action NOT IN ('list','search','add record')) THEN CONCAT(@ErrorMsg,' Invalid action. ')  ELSE @ErrorMsg END); 
	SELECT @ErrorMsg = (CASE WHEN @Action = 'Add Record' AND LEN(@StakeholderName) < 2 THEN  CONCAT(@ErrorMsg,' Stakeholder name required. ') ELSE @ErrorMsg END);
	SELECT @ErrorMsg = (CASE WHEN @Action = 'Add Record' AND LEN(@ServerName) = 0 THEN CONCAT(@ErrorMsg,' Server name required. ') ELSE @ErrorMsg END);
	SELECT @ErrorMsg = (CASE WHEN @Action = 'Add Record' AND LEN(@ApplicationName) = 0 THEN CONCAT(@ErrorMsg,' Application name required. ') ELSE @ErrorMsg END);

	IF @Action = 'Add Record' AND @ErrorMsg IS NULL
	BEGIN

		MERGE INTO dbo.LoginRecordUse as trgt
		USING (	SELECT	R.ServerName, R.LoginName, R.ID AS LoginRecordID, A.ID AS ApplicationTypeID, @StakeholderName As StakeholderName, @Email AS Email, 1 As IsActive
			FROM	dbo.LoginRecord R, dbo.ApplicationType AS A
			WHERE	(R.ServerName = @ServerName OR @ServerName = 'ALL')
				and (R.LoginName = @LoginName)
				AND (R.IsActive = 1)
				AND A.ApplicationName = @ApplicationName
			) AS src
		ON (	trgt.LoginRecordID = src.LoginRecordID 
			AND trgt.ApplicationTypeID = src.ApplicationTypeID
			AND trgt.StakeholderName = src.StakeholderName )
		WHEN MATCHED THEN
			UPDATE SET trgt.Email = src.Email, trgt.IsActive = src.IsActive
		WHEN NOT MATCHED BY TARGET THEN
			INSERT (LoginRecordID,ApplicationTypeID,StakeholderName,Email,IsActive)
			VALUES (src.LoginRecordID,src.ApplicationTypeID,src.StakeholderName,src.Email,src.IsActive
			);

	END;

		SELECT	U.ID AS UseID,
			R.ID AS LoginRecordID,
			R.ServerName,
			R.LoginName,
			U.ApplicationTypeID,
			A.ApplicationName,
			U.StakeholderName,
			U.Email,
			ISNULL(U.IsActive,R.IsActive) AS IsActive,
			master.dbo.fn_varbintohexstr(R.sid) AS SID,
			R.PasswordLastSet,
					DATEDIFF(day,R.PasswordLastSet,getutcdate()) AS Age
			FROM	dbo.LoginRecord R 
			LEFT JOIN dbo.LoginRecordUse U ON R.ID = U.LoginRecordID
-- Without this line, active Logins with a single stakeholder set to inactive will not show in searches
				AND (@IsActive is null or u.isactive = @IsActive)
			LEFT JOIN dbo.ApplicationType A ON U.ApplicationTypeID = A.ID
		WHERE	(@ErrorMsg IS NULL)
			AND (@ServerName = 'All' OR R.ServerName = @ServerName)
			AND (@LoginName = 'All' OR R.LoginName = @LoginName)
			AND (@ApplicationName = 'All' OR A.ApplicationName = @ApplicationName)
			AND (@StakeholderName = 'All' OR U.StakeholderName = @StakeholderName)
			AND (@Email IS NULL OR TRIM(@Email) = '' OR U.Email = @Email)
			AND (@IsActive IS NULL or U.IsActive = @IsActive)
		ORDER BY ServerName, LoginName, ApplicationName, StakeholderName;


	IF @ErrorMsg IS NOT NULL
		PRINT @ErrorMsg;

END
GO

To change the COLOR on the AGE column I will use a function inline:

=Switch(Fields!Age.Value < 50, "Green",
Fields!Age.Value >= 50 and Fields!Age.Value < 61, "Gold",
Fields!Age.Value >= 61, "Maroon")

And my FINAL REPORTS:

Tags: Password Email, SQL Logins

SQL LOGIN PASSWORD MANAGER

GOAL

STEP 1: Set up AUDIT Database

STEP TWO: AuditDW Tables and Procedures

STEP THREE: SSIS Package

STEP FOUR: Emailer

STEP FIVE: SSRS Reporting

PASS DATA COMMUNITY SUMMIT 2024

TempDb Monitor for Snapshot Data

Reducing Size of Overprovisioned Columns

SET UP PARAMETER GROUP on AWS RDS

Archive System-Versioned Temporal Table History Data

Love The SQL

GOAL

STEP 1: Set up AUDIT Database

STEP TWO: AuditDW Tables and Procedures

STEP THREE: SSIS Package

STEP FOUR: Emailer

STEP FIVE: SSRS Reporting

You may also like

PASS DATA COMMUNITY SUMMIT 2024

TempDb Monitor for Snapshot Data

Reducing Size of Overprovisioned Columns

SET UP PARAMETER GROUP on AWS RDS

Archive System-Versioned Temporal Table History Data