Hi Steve,
I installed the code below on our system a couple weeks ago but it caused the following issue:
"We can no longer login to GP in the lab or live using
sa. Every time we try it creates an activity row, then tells us we’re
already logged in and won’t allow you to go further. You have to close
gp, reopen it, delete the activity row to even try again, but when you do …
same result. It creates a new activity row and then tells you that you
are already logged in."
I must have not done something (missed a step??) or installed this incorrectly. Just looking for guidance here and it was my fault for not confining this to our test system first.
Please advise, thank you.
Karen
Trigger name - ddt_ACTIVITY_INS
USE [DYNAMICS]
GO
/****** Object: Trigger [dbo].[ddt_ACTIVITY_INS] Script Date: 11/3/2017 7:07:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ddt_ACTIVITY_INS]
ON [dbo].[ACTIVITY]
FOR INSERT
AS
--Written by Steve Gray 941-747-3669
DECLARE @hostname varchar(255)
declare @body varchar(200)
declare @cc as varchar(200)
set @cc = ''
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
SELECT convert(varchar(255), SERVERPROPERTY('MACHINENAME') )
FROM inserted ins
where ins.USERID = 'sa'
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @hostname
if @@fetch_status <> 0 begin
break
end
select @Body = 'the sa use has just logged on from the ' + @hostname + ' workstation'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default profile',
@recipients = 'kkoehle@valuedrugco.com',
@copy_recipients = @cc,
@body = @Body,
@body_format = 'HTML',
@subject = 'SA access'
END
CLOSE curName
ALLOCATE curName