Hi,

I need to disable the sa account (audit requirements) on a production server that I am not familiar with. What should I check on before I disable it so I dont mess up production

Thanks

Ron

6/19

Just FYI, I've done some tests. A disabled sa can be a DB owner with out causing problems. disabled sa as the owner of a SQL job causes no problems.

• Edited by Ron5443 15 hours 47 minutes ago

Need check If SA is owner of any database ..

Nope. You can disable sa from logging, or even rename sa. That is not going to affect the ownership of the database. A database can be owned by a disabled login. (In fact, it should be owned by a disabled login - but not sa.)

Well, there is of course a very obvious thing to do: set up a trace that captures the Audit:Login event and which is filtered for sa. On SQL 2012 you could also set up a audit session for the same.

Dinesh suggested enabling login auditing for successful logins, and that is also an option, but since that enables auditing for a logins, your errorlog will explode, and you will have to trawl through a lot of data.

But even if you trace, you should communicate to your organisation, since with some amount of luck there is something that only runs once a month that uses sa.

Create a logon trigger that writes to the event log if sa account is ever used. Have your monitoring system pick up the following message from the event log (%sa account was used to logon to the server%)

IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ut_CaptureSa')
DROP TRIGGER [ut_CaptureSa] ON ALL SERVER
GO

CREATE TRIGGER [ut_CaptureSa]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
declare @str varchar(255)
set @str = 'sa account was used to logon to the server ' + + isnull(@@SERVERNAME,'') + ' on ' + cast(getdate() as varchar(50))

IF SUSER_SID(ORIGINAL_LOGIN()) = CAST(0x01 AS varbinary(85))
    RAISERROR(@str,10,1) with log
END;


GO