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.

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 
  • I really wonder why the code that uses 10 rows to check if the login is sysadmin is upvoted, and the same code that user only one row for do the same is downvoted? – sepupic Nov 22 '18 at 11:02
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

  • Please comment why the answer is downvoted. What is incorrect in it? – sepupic Nov 22 '18 at 10:57
  • Not sure either, this should work aswell. Upvoted – Randi Vertongen Nov 22 '18 at 12:04
  • The only issue i see in both answers could be when AD groups are used as logins. My Sysadmin login that belongs to a group was not monitored by either solution. Could be checked with EXEC xp_logininfo 'Login','all'; GO – Randi Vertongen Nov 22 '18 at 12:11
  • 1
    @Randi Vertongen Thank you for your comment, I updated my answer – sepupic Nov 22 '18 at 13:31
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')
  • >>>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 '18 at 13:48
  • However that denial is illusory as the CONTROL SERVER login can impersonate SA and bypass that denial. – David Browne - Microsoft Nov 22 '18 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 '18 at 13:49
  • Yes it is. And any administrator will do. – David Browne - Microsoft Nov 22 '18 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 '18 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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