There are three ways to find who is connected to a database in sqlserver.
First one:
Use the SQL SERVER Management Studio -- Management -- Activity Monitor
This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.
Second One:
Use the built in stored procedure called sp_who2
Run the command exec sp_who2
This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.
Third One:
Third one Use the script
DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int)
INSERT INTO @temp
EXEC sp_who
SELECT COUNT(*) FROM @temp WHERE dbname = 'DB NAME'
From the above methods, you can use them to implement in a sql job to get required notification.
The best method i used is the latest sp_whoisactive by MVP Adam Machanic