September 22, 2008 at 3:39 pm
I need to know when was the last time some users had used the databases. I need this because I want to delete some users that I know no longer work in the company but I don’t know if some applications made by them are using their user info to access de DB.
Anyone have an Idea?
Thanks for the help!
September 22, 2008 at 3:51 pm
There's no way to tell unless you've had some form of custom auditing logging when users log in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 3:55 pm
You could try to audit login events in a SQL Server profiler trace. That wouldn't go backwards for you though.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 22, 2008 at 4:26 pm
What other 3rd party application you know of doing this kind of job (for the feautre)?
September 22, 2008 at 6:29 pm
Do they still have "C2 Auditing" in 2k5? If so, that would probably do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2008 at 6:30 pm
Heh... the "other" way is to simply disable the users... when a proc fails or a user sqawks, then you know you've got a live one... 😛 You laugh! It works everytime...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2008 at 7:35 pm
I was trying to avoid doing that... but it seems that it's going to be the hard way. The sad part is that there are to many users that I believe should be deleted and I will have to do this little by little. It’s going to take time.
Thanks anyway!
And about software for monitoring the db what you guys think are good?
I'm just learning to be a DBA and I need some guidance!
Thanks for the help!
September 23, 2008 at 1:03 am
Jeff Moden (9/22/2008)
Do they still have "C2 Auditing" in 2k5? If so, that would probably do it.
They do. It's maybe overkill for checking when users log in. It logs a lot of information, more on 2005 than on 2000, and if the disk that the trace is running to ever fills up, SQL will shut down immediately.
On a fairly active server I used to work with, on SQL 2000 we were writing a 200MB C2 file about every 15 min. After we upgraded to 2005, we were writing a 200MB file about every 20 sec.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 7:27 am
whow! Why its so dramatic the change?
September 23, 2008 at 8:28 am
fjmorales (9/23/2008)
whow! Why its so dramatic the change?
No idea. I didn't investigate it. We created out own custom trace based on the audit requirements we were under and implemented that.
You may get different results. There may have been oddities with out server/app that resulted in the huge volumes of trace data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 10:02 am
Maybe you could run a job every hour to join sysprocesses to syslogins by sid, and output to a table "recent_logins" ... include the nt_username. Then after a week, the rows in syslogins that are not in "recent_logins" might be unused ? Just a thought after my pondering for a minute or 2. At least give you a starting point.
September 23, 2008 at 10:09 am
Interesting, I'll try it. Thanks!
September 23, 2008 at 10:50 am
just a simple server level login trigger will also do the job for you.
(i.e. do users still log in)
- Out of the box (if you're on SQL2005 SP2)
- every login
- always
You can even choose to use SSB for this (based no events), but I prefer the straight forward approach in this case and have the trigger execute in context of sa and log immediately into a table in masterdb.
This way, I can even log the originating IP address.
USE master;
GO
if object_id('dbo.T_DBA_ConnectionTracker') is null
begin
print 'Table [T_DBA_ConnectionTracker] Created';
CREATE TABLE [dbo].[T_DBA_ConnectionTracker](
[host_name] [varchar](128) NOT NULL,
[program_name] [varchar](128) NOT NULL,
[nt_domain] [varchar](128) NOT NULL,
[nt_user_name] [varchar](128) NOT NULL,
[login_name] [varchar](128) NOT NULL,
[original_login_name] [varchar](128) NOT NULL,
[client_net_address] [varchar](48) NOT NULL,
[tsRegistration] datetime NOT NULL default getdate(),
[tsLastUpdate] datetime NOT NULL default getdate()
) ;
Create clustered index clX_DBA_ConnectionTracker on [dbo].[T_DBA_ConnectionTracker] ([tsRegistration]);
Create index X_DBA_ConnectionTracker on [dbo].[T_DBA_ConnectionTracker] ([login_name]);
end
go
CREATE TRIGGER S_tr_DBA_ConnectionTracker
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
set nocount on
-- need to execute as sa to avoid grant serverstate to public ?
Update T
set [tsLastUpdate] = getdate()
from master.dbo.T_DBA_ConnectionTracker T
inner join sys.dm_exec_sessions ES
on ES.[session_id] = @@spid
and ES.[host_name] = T.[host_name]
and ES.[program_name] = T.[program_name]
and ES.[nt_domain] = T.[nt_domain]
and ES.[nt_user_name] = T.[nt_user_name]
and ES.[login_name] = T.[login_name]
and ES.[original_login_name] = T.[original_login_name]
inner join sys.dm_exec_connections EC
on EC.[session_id] = @@spid
and EC.[client_net_address] = T.[client_net_address] ;
if @@rowcount = 0
begin
INSERT INTO [master].[dbo].[T_DBA_ConnectionTracker] ([host_name], [program_name], [nt_domain], [nt_user_name], [login_name], [original_login_name], [client_net_address] )
select rtrim(ES.[host_name])
, rtrim(ES.[program_name])
, rtrim(ES.[nt_domain])
, rtrim(ES.[nt_user_name])
, rtrim(ES.[login_name])
, rtrim(ES.[original_login_name])
, rtrim(EC.[client_net_address])
from sys.dm_exec_sessions ES
inner join sys.dm_exec_connections EC
on EC.[session_id] = ES.[session_id]
where ES.[session_id] = @@SPID
and not exists (select *
from master.dbo.T_DBA_ConnectionTracker T
where T.[host_name] = ES.[host_name]
and T.[program_name] = ES.[program_name]
and T.[nt_domain] = ES.[nt_domain]
and T.[nt_user_name] = ES.[nt_user_name]
and T.[login_name] = ES.[login_name]
and T.[original_login_name] = ES.[original_login_name]
and T.[client_net_address] = EC.[client_net_address] ) ;
end
/*
Select *
from dbo.T_DBA_ConnectionTracker
*/
END;
/*
another example using SSB : http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/
*/
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
September 23, 2008 at 1:14 pm
OK... Let me see if I understud the query.
It makes a table that gets populated with the info of the user when the triger gets executed?
Should I worry about performance or space by doing this in avery database?
Dont get me wrong i do pareciate your help im just so new to doing DBA Work that I dont whant to make any mistakes that then I can't fix.
September 23, 2008 at 3:51 pm
I used the query for a server on SQL Serv 2005 and until now its been greate but teh server that I whant to verify is in 2000.
Is it possible to have a query like taht on SQL Server 2000?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply