August 22, 2007 at 8:09 am
I am quite new SQL server admin
and was asked very recently to :
produce a list of new databasex users from the database?
by this I mean any new user that has been setup for databasex in the past 3 months (May, June and July)
I tried to look into sysusers but it seem that createdate is not relevant and is not answering this.
August 22, 2007 at 8:25 am
What makes you thinks so?
sysusers.createdate is when that record was created (via, say, sp_addlogin, sp_grantdbaccess, etc.)
What will make sysusers.createdate unreliable is when the database came from another server which requires you to re-link their sids. Some do this with a sp_revokedbaccess/sp_grantdbaccess sequence which will invalidate that date. Sids should be re-linked with a script.
August 24, 2007 at 3:33 am
Thank you veteran for tips.
please correct me if i am wrong.
when grantdbaccess is issued createdate is updated for particular user.
please advice.
August 24, 2007 at 4:23 am
createdate is never updated - otherwise it would lose its meaning.
sp_grantdbaccess will generate an error if the user already exists.
As I said, you need to go through a sp_revokedbaccess/sp_grantdbaccess sequence. The first deletes the record. The second re-creates the record with createdate set to today's datetime.
August 24, 2007 at 6:01 am
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply