Login report of Sysadmin

Question

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.

Answer 1

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

sql server - How to create login and logout trigger for client audit? - Database Administrators Stack Exchange

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;

code to check sysadmin

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 

Answer 2

You should write a logon trigger that will log only sysadmins. Here you can find more about logon triggers: 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())

UPDATE

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

select IS_SRVROLEMEMBER('sysadmin');

without using original_login(). This will work even in case when the AD group is sysadmin, and Windows login is not sysadmin directly but access the server through membership in this AD group

Answer 3

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 )

or

EXISTS (select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER')