Technical Article

Notify All users loged in as SA

,

Procedure that net sends a message to any user who logged in as SA.
IN. had to use this once in the company where SA password was blank for a while and managment did not want to set a password on the account.
You can also set it to work with any username or for users that login to SQL Server from other machines.
With couple of more statements you can also automatically kill the process of that user.

CREATE PROCEDURE Notify_Users_Logged_In_As_SA
AS
	DECLARE @HOST varchar(50)
	DECLARE @Login varchar(50)
	DECLARE @Message varchar(1000)
	DECLARE @Program_Name varchar(500)
	
	DECLARE whois CURSOR 
	FOR SELECT DISTINCT   
		   rtrim(hostname) as hostname,
		   rtrim(loginame) as loginame,
		   program_name
	FROM  master.dbo.sysprocesses
	WHERE
		hostname not in ('','YOURServer') AND
		loginame = 'sa'
		
	OPEN whois
	
	FETCH NEXT FROM whois INTO  @HOST, @Login, @Program_Name
	
	WHILE @@fetch_status = 0 
		BEGIN 
			SELECT @Message = 'xp_cmdshell ' + char(39) + 'net send ' + @host + ' You are logged in to SQL Server as SA from ' + @Program_name + '. Please log off and reconnect with your login name!' + char(39)
			
			EXECUTE (@Message)
			PRINT (@Message)
	
			FETCH NEXT FROM whois INTO @HOST, @Login, @Program_Name
		END
	DEALLOCATE whois

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating