September 28, 2008 at 11:37 pm
I have to audit all sa logons to a database. I think the best would be to use the SQL-Server 2008 auditing feature, but the problem is: can I set up the auditing so that ONLY the sa-logons are audited? I really don't want to audit all other logins....
September 29, 2008 at 9:17 am
Login triggers if you want to only do it for SA?
http://technet.microsoft.com/en-us/library/bb326598.aspx
http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/
http://qa.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/
Although 'sa' account should be disabled on any SQL Server, unless you mean 'sysadmin' role users?
You can also in Server properties -> Security -> Log All login attempts (not just Failed Login ones), then Filter in the SQL Server Log
January 3, 2011 at 12:54 pm
Jerry, i have gone through different articles but i am not sure if that suits my requirement. Basically i need the trigger to send me an email when someone logon's to sql server using "sa" account. Can you please point me to the right article or share the script if you have so? Thanks
January 3, 2011 at 1:21 pm
just tested this, and it works fine, assuming you have dbmail setup with a profile;
--DROP TRIGGER [TR_Logon_Trigger_Track_sa] ON ALL SERVER
GO
CREATE TRIGGER TR_Logon_Trigger_Track_sa
ON ALL SERVER FOR LOGON
AS
BEGIN
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
IF SUSER_SNAME() = 'sa' and @@spid >=50 --avoid system connections
BEGIN
declare @body1 varchar(4000)
set @body1 = '[sa] Login Event Triggered On ' + CONVERT( VARCHAR( 20 ), GETDATE(), 112 ) + ' '
EXEC msdb.dbo.sp_send_dbmail
@profile_name='YourProfileName',
@recipients='you@yourdomain.net',
@subject = '[sa] Login Event Triggered',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER (''sysadmin'') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER(''db_owner'') AS [Is_DB_owner],
IS_MEMBER(''db_ddladmin'') AS [Is_DDL_Admin],
IS_MEMBER(''db_datareader'') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address AS ipaddress,
auth_scheme AS AuthenticationType
FROM sys.dm_exec_connections
WHERE session_id = @@spid
and @@spid >=50 --avoid system connections',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'sa_report.txt',
@query_result_no_padding = 1
END --If
END --TRIGGER
GO
ENABLE TRIGGER [TR_Logon_Trigger_Track_sa] ON ALL SERVER
Lowell
January 3, 2011 at 3:08 pm
Please read my little article regarding the use of logon triggers for this purpose !
There may be some considerable down sides !!
Scope: The drastic caveat with Logon Triggers.
http://qa.sqlservercentral.com/articles/Administration/64974/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 3, 2011 at 8:06 pm
Lowell (1/3/2011)
just tested this, and it works fine, assuming you have dbmail setup with a profile;
--DROP TRIGGER [TR_Logon_Trigger_Track_sa] ON ALL SERVER
GO
CREATE TRIGGER TR_Logon_Trigger_Track_sa
ON ALL SERVER FOR LOGON
AS
BEGIN
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
IF SUSER_SNAME() = 'sa' and @@spid >=50 --avoid system connections
BEGIN
declare @body1 varchar(4000)
set @body1 = '[sa] Login Event Triggered On ' + CONVERT( VARCHAR( 20 ), GETDATE(), 112 ) + ' '
EXEC msdb.dbo.sp_send_dbmail
@profile_name='YourProfileName',
@recipients='you@yourdomain.net',
@subject = '[sa] Login Event Triggered',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER (''sysadmin'') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER(''db_owner'') AS [Is_DB_owner],
IS_MEMBER(''db_ddladmin'') AS [Is_DDL_Admin],
IS_MEMBER(''db_datareader'') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address AS ipaddress,
auth_scheme AS AuthenticationType
FROM sys.dm_exec_connections
WHERE session_id = @@spid
and @@spid >=50 --avoid system connections',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'sa_report.txt',
@query_result_no_padding = 1
END --If
END --TRIGGER
GO
ENABLE TRIGGER [TR_Logon_Trigger_Track_sa] ON ALL SERVER
Thanks. I should have mentioned this before, i am only looking when someone connects using "sa" account not system admin priviledge on sql management studio only, we are using sa account from our webconfig( i know not a good idea) and i do not want to get alert for those. How can i get this? Thanks
January 21, 2015 at 6:18 am
I am aware that this one is a very old thread!!
But have need to clear my doubt and have no other go.
The script works perfect and sends alerts. But my concern is, I am getting blank mails. Could anyone give an explanation, please. So that I will filter it out and receive only when its not blank.
Thanx
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
January 21, 2015 at 6:23 am
as much as I dislike sending emails from within a trigger .....
Double check you are executing the mail query in the correct database ?
Check parameter @execute_query_database
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2015 at 6:39 am
I have created the trigger in master. I suppose the trigger should be executed from master database only.
Please let me know if I have to alter the database
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
January 21, 2015 at 6:52 am
Better yet, disable the sa login like it should be. Having it enabled subjects your servers to attacks. I saw a SQL Saturday presentation where hacking the login was presented in real-time. It was so simple to hack a known login that it was scary. It made me relieved that mine were already disabled. If security is important to you, lock it down.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply