dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Watch-DbaDbLogin

Watch-DbaDbLogin uses SQL Server process enumeration to track logins in a SQL Server table. This is helpful when you need to migrate a SQL Server, and update connection strings, but have inadequate documentation on which servers/applications are logging into your SQL instance. See the Script Center page for more information.

Running this script every 90 seconds for a few weeks should give you a sufficient idea about database and login usage.

 

The data in the SQL table looks like this:

enter image description here

Use the following code to setup the required SQL table

CREATE DATABASE DatabaseLogins
GO
USE DatabaseLogins
GO
CREATE TABLE [dbo].[DbLogins](
[SQLServer] varchar(128),
[LoginName] varchar(128),
[Host] varchar(128),
[DbName] varchar(128),
[Program] varchar(256),
[Timestamp] datetime default getdate(),
)
— Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-Combo] ON [dbo].[DbLogins]
(
[SQLServer] ASC,
[LoginName] ASC,
[Host] ASC,
[DbName] ASC,
[Program] ASC
) WITH (IGNORE_DUP_KEY = ON)
GO

Get-Help

From PowerShell, execute Get-Help Watch-DbaDbLogin -Detailed for more information on this function.

Source Code

Want to see the source code? View Watch-DbaDbLogin.ps1 on GitHub
 

Related commands