We have got a requirement to provide login report from SQL Server for Sysadmin only – This would have log-in date and time when anyone logged into SQL server having sysadmin privilege.
Is there anyway we can get report from SQL server with details of log-ins of only those users who are Sysadmins on the database server.
I guess, in security tab we can enable both Success and Failed Logins however enabling this would add lot of overhead on server, currently we have enabled this only for failed logins. And I don’t know if there is anyway to enable success login only for sysadmin and not for others.
Any help would be appreciated.
I’d probably look into LOGON trigger
and combined with checking login for sysadmin
If logon user IS sysadmin, log it; else skip
Obviuosly, monitor for performance impact
CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON AS BEGIN IF SUSER_SNAME() = 'SomeUser' INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid) VALUES (SUSER_SNAME(), GETDATE(), @@SPID); END;
SELECT name,type_desc,is_disabled, create_date FROM master.sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1 ORDER BY name
LOL, and I just found out I have such script, see below
CREATE TRIGGER trigLogon_CheckForSysAdmin ON ALL SERVER FOR LOGON AS BEGIN IF EXISTS ( SELECT sp.principal_id FROM sys.server_role_members srm JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id WHERE role_principal_id = ( SELECT principal_id FROM sys.server_principals WHERE NAME = 'sysadmin') AND ORIGINAL_LOGIN() = sp.NAME) BEGIN INSERT INTO DBAWork.dbo.AuditSysAdminLogin (EventTime, ServerLogin) VALUES (GETDATE(), ORIGINAL_LOGIN()) END; END; GO
You should write a
logon trigger that will log only
Here you can find more about logon triggers:
And here is a practical example of its use:
SQL Server – Login Auditing using Logon Triggers
P.S. To find out if the login is a sysadmin you can use this code:
select is_srvrolemember ('sysadmin',original_login())
If the logon trigger does not use
EXECUTE AS clause (and in case of OP this is true), it’s executed under the current
login. So to check out if the login is
sysadmin is sufficient execute
original_login(). This will work even in case when the
AD group is
Windows login is not
sysadmin directly but access the server through membership in this
A better check than sysadmin membership is to check if the login has CONTROL SERVER permissions. All sysadmins have CONTROL SERVER and any login with CONTROL SERVER can impersonate SA, so CONTROL SERVER is not materially weaker than sysadmin for control and audit purposes. And it closes a security hole where a user could evade audit while logging in with administrative permissions.
Check the permission like:
IF (select HAS_PERMS_BY_NAME(null,null,'CONTROL SERVER') = 1 )
EXISTS (select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER')