codingoutloud
3/12/2019 - 5:27 PM

check-azure-sql-db-logins.sql

-- Turn on Audit Logging to Blob for your Azure SQL Database. Then you can query who has logged in. 
-- The example below assumes DB Server-level audit logging. Details will vary slightly for Database-level audit logging.
-- The example below shows who logged in so far today.
-- Change "-0" to "-1" to look at yesterday (from a UTC perspective, not your local timezone).
-- Change "-0" to "-100" to look at 100 days ago.
SELECT FORMATMESSAGE('%s (%s)', CAST(DATEADD(day, -0, CONVERT(date, SYSUTCDATETIME())) as varchar), 
                      DATENAME(WEEKDAY, DATEADD(day, -0, SYSUTCDATETIME()))), 
       server_principal_name, 
       COUNT(server_principal_name) as 'Logins'
FROM sys.fn_get_audit_file(FORMATMESSAGE('https://<MYBLOB>.blob.core.windows.net/sqldbauditlogs/<MYDBSERVER>/<MYDB>/SqlDbAuditing_ServerAudit/%s/', 
                                         CAST(DATEADD(day, -0, CONVERT(date, SYSUTCDATETIME())) as varchar)),default, default)
WHERE (event_time >= CAST(CONVERT(date, SYSUTCDATETIME()) as datetime2))AND (action_id = 'DBAS')
GROUP BY server_principal_name 
HAVING COUNT(server_principal_name) > 0