SQL Server data change and Security

Question

in our environ any user who login with sa they can change any table data.

so i write trigger to capture changed data like who change, from which IP etc this

CREATE TRIGGER [TRG_Users]
ON [dbo].[UserRights] AFTER INSERT, UPDATE, DELETE
AS

DECLARE @strIP VARCHAR(MAX)

SET @strIP=(SELECT dbo.GetCurrentIP())

IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
    --PRINT 'Update happened';
    INSERT INTO Logger(IPAddress,Status)
    VALUES (@strIP,'UPDATE')
ELSE
IF EXISTS (SELECT * FROM INSERTED)
    --PRINT 'Insert happened';
    INSERT INTO Logger(IPAddress,Status)
    VALUES (@strIP,'INSERT')
ELSE
    --PRINT 'Delete happened';
    INSERT INTO Logger(IPAddress,Status)
    VALUES (@strIP,'DELETE')


CREATE FUNCTION [dbo].[GetCurrentIP] ()  
RETURNS varchar(255)  
AS  
BEGIN  
   DECLARE @IP_Address varchar(255);  

   SELECT @IP_Address = client_net_address  
   FROM sys.dm_exec_connections  
   WHERE Session_id = @@SPID;  

  Return @IP_Address;  
  END

but the problem is user can change data after disabling the trigger on specific table. so trigger will not fire and user can seamlessly change data.

so guide me what is the best way to capture data change and log them. so no one can bypass the security. please do not tell me disable sa account because i am looking for different approach to capture the change data. is there any secure way exist in sql server 2005/2008 if yes then please discuss here. thanks

If some is logged in as `SA` you cannot conrtol their activities, Or even monitor their activities if they dont want be to monitered, and giving `SA` role to many users is never a good idead anyway. Assign your users/developers an appropriate role enough for their needs and then create this Trigger and takeaway their permission to Disable on this trigger. — M.Ali, Apr 11 '14 at 15:16
You seem well aware of the problem of letting others connect as `sa`. I'm not sure how you'd expect anyone to solve your issue. At best, you'd be playing a cat/mouse game, always trying to stay one step ahead. The best solution is the one you apparently don't want to hear--don't let others connect as `sa` (or with any other login that has `sysadmin` privileges). — Dave Mason, Apr 11 '14 at 15:19

score 3 · Accepted Answer · answered Apr 14 '14 at 14:22

Problem with SA is that all security checks are skipped for this login (or any other login in sysadmin role for that matter). So, you can't revoke any privilege from SA and also there's nothing you can do on instance level that SA cannot bypass.

Like others already said, don't let anyone login as a sysadmin unless there's real sysadmin work to be done. Best practice is to disable SA login altogether.

On a cnstructive side, your best bet otherwise is to create a SQL Server Audit session and use Windows security log as a target. This way you'll at least know who and when stopped the audit.

SQL Server data change and Security

1 Answers1