audit the hosts on a server

Question

audit the hosts on a server

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323741 More actions · Answer 1

rather than tracking where the inapproprate acces sis coming from, you could change the passwords for those two users, or maybe use a logon trigger to prevent access for those two usernames form anywhere except the authorized servers.

note that a trigger llike this needs to be tested....you might block out access for users that should have access.

CREATE TRIGGER logon_trigger_not_FromTheRightPC

ON ALL SERVER FOR LOGON

AS

BEGIN

IF suser_name() IN('sam','jam' )

AND host_name() NOT IN('auth1','auth2')

--raise an error, which goes to the error log

RAISERROR('Unauthorized use of login from inpermissible host.', 16, 1)

--prevent connection with these two usenrames

ROLLBACK

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

brave heart SSC Enthusiast Points: 134 More actions · Answer 2

lowell,

thank you very much for the reply.I really appreciate it.I was asked to just monitor the un authorized activity and file it into a table using the sytem tables in a server.thanks in advance.

Lowell SSC Guru Points: 323741 More actions · Answer 3

in that case, look at this article, which shows how to audit access to the databases with a server side trace:

http://www.sqlservercentral.com/articles/Administration/trace/872/

once that is in place, you can insert the results into a temp table, query it, roll it up with groups, etc to count unauthorized access and stuff.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shinoj Ramanathan SSC Eights! Points: 894 More actions · Answer 4

You can select it this way and insert it into a table

SELECT COALESCE(@HostNames + ',', '') + host_name

, COALESCE(@LoginNames + ',', '') + login_name

FROM sys.dm_exec_sessions

WHERE

program_name LIKE '%Management Studio%'

AND login_name NOT IN ('sam', 'jam')

This can be scheduled

Shinoj