There are three ways to find who is connected to a database in sqlserver.
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.
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 Use the script
DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int)
INSERT INTO @temp
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