script to find the frequent used login information in the sql

Pol 86 Reputation points
2023-03-27T14:39:12.6666667+00:00

Hi,

Kindly some one share the script or query to get the login information used for the sql database in the instance.

InstanceName,DatabaseName,HostName,LoginName,Program,date used etc

Thanks,

Jo

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bjoern Peters 8,781 Reputation points
    2023-03-27T16:05:24.8+00:00

    Hi Jo,

    Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

    Thank you for posting your question; I'll be more than glad to help you out.

    There is no "ready-to-use" script or built-in functionality... you have to create your own "monitoring" solution for it

    For example, create an Azure automation workflow that connects every x minutes to your database and writes the result of sp_who into a table, then you can run your report on it...

    https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql?view=sql-server-ver16

    Or you write automation using a login TRIGGER

    https://learn.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver16

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments
  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-03-28T02:07:35.68+00:00

    Hi @Pol

    In addition to the above methods, you can also get information on the system view.

    You can refer to this official document.

    https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-ver16

    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments