User Status

[dBjason]

SQL Server 7

Hello,

Is there any way to tell when the last time a user accessed a SQL Server database? I have one database with hundreds of users in it (some of them are no longer with us), and would like to start dropping thier user accounts if they haven't used the database in [X] number of months.

Thanks,
Jason

 

[gmmastros]

I'm not sure about SQL 7, but in 2000, there is a syslogins table (in the master database) that stores information you may be interested in.

In 2000...

Select * from Master..syslogins



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dBjason]

Derp! SQL Server 2000

Thanks George,

I've got the table up, but can't tell what column I need.

CreateDate is the date the login was added, UpdateDate is the date the login was updated [I've no idea what that means], and AcctDate is for "internal use only" [even less of an idea what that means].

Would the UpdateDate be the last time a person logged on, or would it be the last time SA updated thier rights?

 

[mrdenny]

SQL doesn't track the last time a user loged into the database. Are you granting rights via NT login, or via SQL login?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

You can run profiler for a few days, and see who IS logged in on a regular basis... and drop the non-users

 

[Catadmin]

Or you could deny access to accounts you don't think are logged in and see who complains. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dBjason]

Looks as though I'll have to run a trace and go from there.

Denny, we're using NT logins with Windows Authentication. Might there be a way to find out through the network login?

Thanks,
Jason

 

[Catadmin]

Use network domain policies to audit the logins or check the SQL Server's Event viewer / Security to see if anything was logged there.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dBjason]

I'll give that a shot. Thanks!

 

[mrdenny]

There's an easier way than that.

The SQL command sp_validatelogins will give you a list of all NT Account and Domain Groups which no longer exist on the domain.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com