1

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.

3 Answers 3

2

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 
0
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

0
1

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')
6
  • >>>CONTROL SERVER is not materially weaker than sysadmin<<< Any member with CONTROL server can be denied any kind of permission, while sysadmin can not
    – sepupic
    Nov 22, 2018 at 13:48
  • However that denial is illusory as the CONTROL SERVER login can impersonate SA and bypass that denial. Nov 22, 2018 at 13:48
  • On other hand, the only IMPERSONATE on sa is equal to CONTROL on server and is even more difficult to determine
    – sepupic
    Nov 22, 2018 at 13:49
  • Yes it is. And any administrator will do. Nov 22, 2018 at 13:53
  • Besides, you can have a login with granted CONTROL SERVER and DENIED IMPERSONATE ANY LOGIN. And this login CAN NOT bypass this deny. So may be it make sense to control if a login can impersonate sysadmin instead of having CONTROL SERVER
    – sepupic
    Nov 22, 2018 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.